Forum Discussion

aellison's avatar
aellison
Icon for Helper I rankHelper I
8 years ago
Solved

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,

 

Need Help
  • BILASolution's avatar
    BILASolution
    8 years ago

    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

2 Replies

  • BILASolution's avatar
    BILASolution
    Icon for Solution Specialist rankSolution Specialist
    8 years ago

    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