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?