Distinct CountIF with ALLEXCEPT vs. ALL

[DatAlessia]

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!

[v-xinruzhu-msft]

Hi @DatAlessia 

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.

[DatAlessia]

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.

[v-xinruzhu-msft]

Hi @DatAlessia 

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.

[TomasAndersson]

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.

[DatAlessia]

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?