Forum Discussion
Evaluation context - mixed filter and row context
Really great question. I've had to go back to page 316 onwards of "The Definitive Guide to DAX" to be sure.
Key starting point is that Context Transition happens before further filters in CALCULATE and combined by Overwrite.
In Point 2 {Product:”B”, Price:200} gets added into filter context. The further filter then results in [{Product:”B”, Price:200},{Product:”C”, Price:300}].Here DAX overwrites the existing filter resulting in [{Product:”B”, Price:200},{Product:”C”, Price:300}]
In Point 5. I found much harder to get straight in my head! You're right that in the FILTER Sales[Price] is evaluated in the context on the original row and results in {A, B, C}. This still overwrites the existing filter coming from context transition but only in the Product Column. You're left with Product IN {A, B, C} AND the price that has come from the current row. For the second row {A, B, C} AND 200 results {B, 200}.Have a read of Understanding Context Transition - SQLBI
Can also highly recommend "Advanced Evaluation Context" in The Definitive Guide to DAX.
I'll do my best! Not sure I can explain it any better though.
The way I'd see it is as follows:
Original filter is:
However the default behaviour of calculate is to first remove any existing filter on a column (overwrite), then add new one back in before finally combining with anything that exists using intersect.
1) Remove:
2) Add in:
3) Combine with intersect
Thanks a lot for your time. But this is exactly strange to me.
Before I compute value SumYear := SUMX (VALUES (DimTime[CalendarYear]), [TotalSales]) for subtotal 2021 in matrix, there are two original filters on visual level to me:
1) Filter set on slicer:2) Row (= filter) in matrix for subtotal 2021: DimTime[CalendarYear] = 2001.
Now, I don't understand why these 2 filters in original filter context are not merged before they flows to CALCULATE to this:
DimTime[CalendarYear] = 2001 && (DimTime[MonthName] = "July" || DimTime[MonthName] = "August"))But as you say, it looks like filter on year in matrix ( = 2021) breaks and overwrites year in whole arbitrarily shaped filter coming from slicer.
I though that filters on visual are merged to original filter context before it flows to CALCULATE to compute SumYear . In that example from linked article I thought that only CALCULATE after context transition of VALUES (DimTime[CalendarYear]) can overwrite filter on same year column and break arbitrarily shaped filter.
It is confusing because in another similar example here https://www.sqlbi.com/articles/when-to-use-keepfilters-over-iterators/ the subtotals in matrix with similar arbitrarily shaped filter are correct. Only grand total was not correct due to CALCULATE + context transition.
Thank you.
I created another simple example (mixed filter context + context transition) to make it easier to verify the resulting values.
There is fact table Sales:and calendar table with corresponding relationship:
Then there are measures:
SalesAmount = SUMX(Sales, Sales[Price]*Sales[Amount])
SumMonth = SUMX(VALUES(dDate[Month]),[SalesAmount])
SumYear = SUMX(VALUES(dDate[Year]),[SalesAmount])I created arbitrarily shaped filter and here is the result:
I would assume that:
1) subtotal for SumYear in 2019 will be 4, not 22 (sum for months 1, 2, 11, 12 of 2019)
2) subtotal for SumYear in 2020 will be 40, not 22 (sum for months 1, 2, 11, 12 of 2020)The evaluation context is not cleart to me still 😞
Can you share your demo file?
