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