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 the red Products bought:

 

 

Column1 =
CALCULATE (
    DISTINCTCOUNTNOBLANK ( 'Table'[Product] ),
    ALLEXCEPT ( 'Table', 'Table'[CustomerID] ),
    'Table'[Colour] = "RED"
)

 


This other calculated column should do the same trick, but without the DISTINCT:

 

 

Column2 =
VAR ThisId = 'Table'[CustomerID]
RETURN
    CALCULATE (
        COUNTAX ( 'Table', 'Table'[Product] ),
        FILTER ( ALL ( 'Table' ), 'Table'[CustomerID] = ThisId ),
        'Table'[Colour] = "RED"
    )

 

 

As it turns out, if I try to put a DISTINCTCOUNTNOBLANK, it crashes the report because it starts evaluating without returning the result.

 

I would like your support to understand the reason why ALLEXCEPT works with distinct, whilist the second solution does not.
Also, is there any other way to write this code other than the "Column1" example? It's the ALLEXCEPT use in there which I find a bit counterintuitive!


I'm trying to understand the meaning behind the DAX formulas I find all over the web, so any help will be appreciated!

 

Thanks a lot!

Need Help

5 Replies

  • TomasAndersson's avatar
    TomasAndersson
    Solution Sage
    3 years ago

    Hi!

    Not sure about your first question, do you mean that below doesn't work for you?

    Column3 =
VAR ThisId = 'Table'[CustomerID]
RETURN
    CALCULATE (
        DISTINCTCOUNTNOBLANK('Table'[Product] ),
        FILTER ( ALL ( 'Table' ), 'Table'[CustomerID] = ThisId ),
        'Table'[Colour] = "RED"
    )

     I tried to replicate with some own test data i made, but mine didn't crash so maybe it's structured differently.

     

    An alternative to using ALLEXCEPT() would be above, so I'm trying to understand where it crashes for you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi!

      Thanks for your support.

       

      I'm actually relieved to hear that the CALCULATE with ALL should work. When I say that for me it crashes the report I mean that when I put the formula in a new calculated column and then press enter, it just loads it endlessly and I end up having to force close PowerBI. The solution with ALLEXCEPT, instead, works fine.

       

      I noticed that if I put some bogus word in the "Colour" condition, such as "abcd", it actually returns instantly a (blank) result.

       

      At this point I guess might be a problem of number of rows? I don't know. The model it's not complicated and does not have a lot of relationships between tables or calculated columns, but it is over 500k rows. Could this be the problem?

       

       

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