DAX CALCULATE does not respect Pivot Table row context

Question

Excel Power Pivot

Lookup Table = Calendar; Data Table = Sales

Pivot table Rows: Calendar[Year]

Pivot table Values: [Measure] = CALCULATE(SUM(Sales[Amount]); Calendar[Date] < DATE(2003;01;01))

Measure does not respect the filter context Year and returns:
Year 2002 -> sum of sales from the earliest date in Calendar to 01/01/2003;
Year 2003 -> the same

As Date column is not in the filter context - Year , I would expect the Date filtration added to the Filter context not override it. The Measure should return then:
Year 2002 -> sum of 2002 sales;
Year 2003 -> 0

Why does the actual result differ from the expected?

The question is not about how to fix the formula. It is about why it happens. Why does the formula ignore the row labels of the pivot table.? — seaman47
– seaman47, Commented Aug 18, 2024 at 10:05

Mosasaurier · Accepted Answer · 2024-09-22 12:58:51Z

I have spent half a day trying to figure this out and could pinpoint the source of the issue to the format of the Calendar[Date] column. As soon as you switch its format away from a date format to a number format, the filter context on the Calendar[Year] column is respected.

After further research, I have found the following passage inside this article https://www.sqlbi.com/articles/mark-as-date-table/:

"In order to simplify the usage of time intelligence functions, the DAX engine makes an assumption when two tables are related through a column of Date data type: When a filter is applied on the key of the relationship – Date[Date] in this example – the new filter overrides any other filter on the Date table. It basically applies a REMOVEFILTERS ( Date ) to the filter context every time you apply a filter on the Date[Date] column. This behavior occurs automatically only when the relationship is based on a column of Date data type."

It seems insane to me that such a detail is nowhere mentioned in the official documentation (or maybe I just haven't found it yet).

Cp Gowtham · Accepted Answer · 2024-08-18 10:08:40Z

0

The measure is ignoring the row context of the Pivot Table because CALCULATE creates a new filter context, which overrides the existing row context. To ensure the year filter is respected, it must be explicitly included in the FILTER function within CALCULATE.

edited Aug 18, 2024 at 10:08

answered Aug 18, 2024 at 9:51

Cp Gowtham

3611 silver badge7 bronze badges

9 Comments

seaman47 Over a year ago

I quite understand that it behaves like this. But I would like to know why it happems. Why does it ignore the row context of the pivot table?

Cp Gowtham Over a year ago

The measure is ignoring the row context of the Pivot Table because CALCULATE creates a new filter context, which overrides the existing row context. To ensure the year filter is respected, it must be explicitly included in the FILTER function within CALCULATE.

seaman47 Over a year ago

CALCULATE can only reset filter context if the column used in the filter expression is ALREADY in the filter context. It is not the case here.

Cp Gowtham Over a year ago

You're correct. CALCULATE only affects filters already in context. Since Calendar[Year] isn’t, you have to add it manually with FILTER. That's why the measure ignores the year by default.

seaman47 Over a year ago

<<you have to add it manually with FILTER>> Why?? CALCULATE only touches Date column. Hence Year filter context should be alive.

|

Collectives™ on Stack Overflow

DAX CALCULATE does not respect Pivot Table row context

2 Answers 2

Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related