Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Continued Contributor
Continued Contributor

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

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
Continued Contributor
Continued Contributor

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.