Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Distinct CountIF with ALLEXCEPT vs. ALL

Hi everybody,   I need a hand understanding the DAX code for a "(distinct) count if".   I have this calculated column, which works correctly and returns a distinct count for every CustomerID for ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous 

You can try the following code:

Column1 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Product] ),
    Filter( 'Table', 'Table'[CustomerID]=EARLIER('Table'[CustomerID]),
    'Table'[Colour] = "RED",'Table'[Product]<>Blank())
)

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi!

     

    Thanks for your help!

    Unfortunately even this formula crashes the report:

     

    But I think at this point it must have something to do with the model itself.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Anonymous 

      All() will remove filters and just keep what you want, but allexcept() will be inefficent if you have complex relationship or large data. you can refer to the following link:

      Solved: 10x slower with ALLEXCEPT vs - Microsoft Power BI Community

       

      Best Regards!

      Yolo Zhu

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.