CODEWITHSUNDEEP
powerbidax
Rowan Sciban
Rowan Sciban

Reputation: 1

DAX ALL using the date dimension

Within Power BI Desktop (Version: 2.39.4526.362 64-bit (September 2016), I have written a DAX Statement that behaves differently when the column is sorted by another vs when it is not sorted by another.

Measure:

Sum of Sales Across All Months =
CALCULATE ( SUM ( SalesAmount ), ALL ( 'Date'[MonthName] ) )

When the MonthName column is unsorted by another column, the measure behaves as I expect. Eliminating the filter context of the MonthName column. However, as soon as I set the MonthName column to be sorted by another column (e.g., MonthNumber) the "ALL" context reset is lost and it reverts back to the MonthName context.

Does anyone know if this is a bug or if I am misunderstanding something?

Thanks!

Upvotes: 0

Views: 2146

Answers (1)

Leonard
Leonard

Reputation: 2578

When one column sorts by another column, the DAX that Power BI generates includes the sort-by column, even though it's not visible in your visual. Therefore, for the measure to behave as you'd expect, you need to remove the filter context from both columns, even though only one is visible:

Sum of Sales Across All Months =
    CALCULATE (
    SUM ( SalesAmount ),
    ALL ( 'Date'[MonthName] ),
    ALL ( 'Date'[MonthNumber] )
)

It's unintuitive, but I don't know that it's a bug. There's a blog post that describes the behaviour you're seeing here: https://blog.crossjoin.co.uk/2015/12/15/power-bi-desktop-sort-by-column-and-dax-calculations-that-use-the-all-function/

Upvotes: 1

Related Questions