Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to perform counts based on number of days that a store has no safety incidents. The first list shows the store names, the date, and the number of times they reported Safe (No Incidents) in a day. If the store has any incidents, it's marked with a zero. Any zero makes the day considered not safe. This is not really a count of Safe, but rather a count of dates, with at least 1 safe, but no zeros on that specific date. Here is the example data:
StoreName | Date | Safe |
StoreA | 1/1/2017 | 1 |
StoreA | 1/2/2017 | 1 |
StoreA | 1/3/2017 | 1 |
StoreA | 1/3/2017 | 1 |
StoreA | 1/4/2017 | 1 |
StoreB | 1/1/2017 | 1 |
StoreB | 1/2/2017 | 1 |
StoreB | 1/2/2017 | 0 |
StoreB | 1/3/2017 | 1 |
StoreC | 1/1/2017 | 1 |
StoreC | 1/1/2017 | 0 |
StoreC | 1/1/2017 | 1 |
StoreC | 1/2/2017 | 0 |
StoreC | 1/3/2017 | 1 |
The results are to count the number of Safe days only.
StoreName | 1/1/2017 | 1/2/2017 | 1/3/2017 | 1/4/2017 | Total |
StoreA | 1 | 1 | 1 | 1 | 4 |
StoreB | 1 | 1 | 2 | ||
StoreC | 1 | 1 | |||
Total | 2 | 1 | 3 | 1 | 7 |
Any help would be greatly appreciated,
Solved! Go to Solution.
Hi @aellison
Try this measure...
Ind Safe = SUMX ( SUMMARIZE ( Safe; Safe[StoreName]; Safe[Date]; "Ind Safe"; PRODUCT ( Safe[Safe] ) ); [Ind Safe] )
Or
Ind Safe = SUMX ( SUMMARIZE ( Safe; Safe[StoreName]; Safe[Date]; "Ind Safe"; IF(PRODUCT(Safe[Safe]) =0;BLANK();PRODUCT(Safe[Safe])) ); [Ind Safe] )
The matrix looks like...
Regards
BILASolution
Hi @aellison
Try this measure...
Ind Safe = SUMX ( SUMMARIZE ( Safe; Safe[StoreName]; Safe[Date]; "Ind Safe"; PRODUCT ( Safe[Safe] ) ); [Ind Safe] )
Or
Ind Safe = SUMX ( SUMMARIZE ( Safe; Safe[StoreName]; Safe[Date]; "Ind Safe"; IF(PRODUCT(Safe[Safe]) =0;BLANK();PRODUCT(Safe[Safe])) ); [Ind Safe] )
The matrix looks like...
Regards
BILASolution
Perfect ...thank you ..
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |