cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TL5866 Frequent Visitor
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

Accepted Solutions
dramus Established Member
Established Member

Re: CALCULATE with ALL(table[column])

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

View solution in original post

Super User
Super User

Re: CALCULATE with ALL(table[column])

@TL5866

 

I get 6 when I use your MEASURE Smiley Surprised

 

It seems perfect

 

All.png

View solution in original post

4 REPLIES 4
dramus Established Member
Established Member

Re: CALCULATE with ALL(table[column])

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

View solution in original post

Super User
Super User

Re: CALCULATE with ALL(table[column])

@TL5866

 

I get 6 when I use your MEASURE Smiley Surprised

 

It seems perfect

 

All.png

View solution in original post

TL5866 Frequent Visitor
Frequent Visitor

Re: CALCULATE with ALL(table[column])

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

Re: CALCULATE with ALL(table[column])

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 ?

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 166 members 1,672 guests
Please welcome our newest community members: