DAX pivot counts with exception

[aellison]

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,

[BILASolution · Accepted 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

[aellison]

Perfect ...thank you ..