topic Re: Using VALUES + ALL instead of just ALL. Resolution included - Need to know WHY it works in DAX Commands and Tips

Using VALUES + ALL instead of just ALL. Resolution included - Need to know WHY it works

prill22ta — Fri, 11 Nov 2022 01:53:11 GMT

Hi there,

I recently ran into the following issue. My team has since resolved it (included) but we don't know 'why' it is the resolution.

The ask: Calculate a Distinct Count of Values where the Primary Key IN a list of PK's defined by a subquery.
The strange thing is, that when we ran the subquery through DAX Studio by itself, it returned the expected results.
Thank you for taking a look!

The DAX that WORKED:

VAR _subq =

CALCULATETABLE (

VALUES( table[field] ),

ALL( table ),

table[field2] = "Yes"

)

VAR _count = CALCULATE (

DISTINCTCOUNT( table[field] ),

table[field] IN _subq)

RETURN

_count

The DAX that DID NOT WORK:

VAR _subq =

CALCULATETABLE (

ALL( table[field] ),

table[field2] = "Yes"

)

VAR _count = CALCULATE (

DISTINCTCOUNT( table[field] ),

table[field] IN _subq)

RETURN

_count

Re: Using VALUES + ALL instead of just ALL. Resolution included - Need to know WHY it works

FreemanZ — Fri, 11 Nov 2022 02:29:25 GMT

For the first dax code, is it a measure? what filter context does it have?

Re: Using VALUES + ALL instead of just ALL. Resolution included - Need to know WHY it works

prill22ta — Fri, 11 Nov 2022 03:52:32 GMT

Both versions are a measure.

Re: Using VALUES + ALL instead of just ALL. Resolution included - Need to know WHY it works

v-jialluo-msft — Mon, 14 Nov 2022 06:33:31 GMT

^{Hi prill22ta ,}

^{Using a single column argument, ALL returns all the unique values of the column.}

CALCULATETABLE ( ALL( table[field] ), table[field2] = "Yes" )

^{So this statement returns the unique value of the table[filed] column, and the following table[filed2]="Yes" does not play a filtering role for the previous statement,}^{because the ALL function ignores all filters}

ALL – DAX Guide

^{Best Regards,}

^{Gallen Luo}

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