CALCULATE with ALL(table[column])

[TL5866]

Hi,

This is supposed to be a simple one, but I cannot figure out how to make it work.

Here a simple table:

CustomerID	Region	Industry
C1	Unknown	Agriculture
C2	Islands	Mining
C3	Islands	Manufact.
C4	Scotland & NI	Agriculture
C5	Scotland & NI	Mining
C6	Unknown	Manufact.

I have created the following measure:

#CustomersAcrossRegions = CALCULATE(DISTINCTCOUNT(table[CustomerID]), ALL(table[Region]))

Here is the following result:

Region	#CustomersAcrossRegions
Scotland & NI	2
Islands	2
Unknown	2

I was actually expecting to get "6" in each row, as I've removed the context filter on [Region]. What am I missing ?

How can I effectively get the count of customers for all the regions, while still being sensistive to other filters in the context ?

Thanks in advance for your help

[dramus · Accepted solution]

Just reproduced this. I am getting 6. Do you have another filter on there somewhere?

[Zubair_Muhammad · Accepted solution]

@TL5866

I get 6 when I use your MEASURE

It seems perfect

[TL5866]

Hi both,

Thanks for your feedback.

The table was actually loaded from a SQL DB. It turns out that the duplicating the query and running the measure on it, is giving the correct result. So I now have two identical tables in my PBIX, giving different measure results !

I will check with MS if a bug needs to be reported.

[TL5866]

Hi,

I've done some more digging on this, and finally found the culprit: Sort by Column.

When there is no sort order in the model, then I get the expected result, However, I had a "RegionSortOrder" column set in the "sort by column" on "Region". The "#CustomersAcrossRegions" measure only cancels the "Region" filtering, which is leading to the unexpected result as shown above. The fix is to add "RegionSortOrder" in my measure:

#CustomersAcrossRegions = CALCULATE(DISTINCTCOUNT(table[CustomerID]), ALL(table[Region]), ALL(table[RegionSortOrder]))

While I can understand how it works behind the scenes in the DAX query, I would consider it a misleading side effect, rather than the expected behavior: why should I care about a dimension's sort order in my measure ?

[dramus · Accepted solution]

Just reproduced this. I am getting 6. Do you have another filter on there somewhere?