Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aellison
Helper I
Helper I

DAX pivot counts with exception

 

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:

 

 

StoreNameDateSafe
StoreA1/1/20171
StoreA1/2/20171
StoreA1/3/20171
StoreA1/3/20171
StoreA1/4/20171
StoreB1/1/20171
StoreB1/2/20171
StoreB1/2/20170
StoreB1/3/20171
StoreC1/1/20171
StoreC1/1/20170
StoreC1/1/20171
StoreC1/2/20170
StoreC1/3/20171

 

 

The results are to count the number of Safe days only.

 

 

StoreName1/1/20171/2/20171/3/20171/4/2017Total
StoreA11114
StoreB1 1 2
StoreC  1 1
Total21317

 

 

Any help would be greatly appreciated,

 

1 ACCEPTED SOLUTION
BILASolution
Solution Specialist
Solution Specialist

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...

 

1.png

 

 

Regards

BILASolution

View solution in original post

2 REPLIES 2
BILASolution
Solution Specialist
Solution Specialist

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...

 

1.png

 

 

Regards

BILASolution

Perfect ...thank you .. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.