Forum Discussion
CALCULATE with ALL(table[column])
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
Just reproduced this. I am getting 6. Do you have another filter on there somewhere?
4 Replies
Just reproduced this. I am getting 6. Do you have another filter on there somewhere?
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.
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 ?
