cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TL5866
Frequent Visitor

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:

CustomerIDRegionIndustry
C1UnknownAgriculture
C2IslandsMining
C3IslandsManufact.
C4Scotland & NIAgriculture
C5Scotland & NIMining
C6UnknownManufact.

 

I have created the following measure:

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

Here is the following result:

Region#CustomersAcrossRegions
Scotland & NI2
Islands2
Unknown2

 

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

2 ACCEPTED SOLUTIONS
dramus
Responsive Resident
Responsive Resident

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

View solution in original post

Zubair_Muhammad
Community Champion
Community Champion

@TL5866

 

I get 6 when I use your MEASURE Smiley Surprised

 

It seems perfect

 

All.png


Regards
Zubair

Please try my custom visuals

View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

@TL5866

 

I get 6 when I use your MEASURE Smiley Surprised

 

It seems perfect

 

All.png


Regards
Zubair

Please try my custom visuals

View solution in original post

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
Frequent Visitor

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
Responsive Resident
Responsive Resident

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

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.