Forum Discussion

TL5866's avatar
TL5866
Frequent Visitor
8 years ago
Solved

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: CustomerID Region Industry C1 Unknown Agriculture C2 Islands Mining ...
Need Help
  • dramus's avatar
    dramus
    8 years ago

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

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    TL5866

     

    I get 6 when I use your MEASURE :smileysurprised:

     

    It seems perfect

     

TL5866's avatar
TL5866
Frequent Visitor
8 years ago

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's avatar
TL5866
Frequent Visitor
8 years ago

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 ?