cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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 Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

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.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 3,991 guests
Please welcome our newest community members: