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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors