This is supposed to be a simple one, but I cannot figure out how to make it work.
Here a simple table:
|C4||Scotland & NI||Agriculture|
|C5||Scotland & NI||Mining|
I have created the following measure:
#CustomersAcrossRegions = CALCULATE(DISTINCTCOUNT(table[CustomerID]), ALL(table[Region]))
Here is the following result:
|Scotland & NI||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
Solved! Go to Solution.
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.
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 ?
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.