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.
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.
This definition I think makes it clear from The Definitive Guide to DAX:
To compute A overwrite B, DAX does two operations:
1) It removes from all the filters in B the columns filtered in A, generating a new filter context that we call B-Cleaned.
2) It intersects A with B-Cleaned.
(You can think of intersects as AND )
Thank you for your answer.
There is one more confusing thing to me abot how filter context flows from visuals to CALCULATE in this atricle
I thought that only filter arguments in CALCULATE can overwite original filter context from visual (slicer, matrix, ...) or context transition on same filtered columns.
I assumed that filters on slicers and dimension value in row/column in matrix in visual are merged together. But in the linked article, it is not clear to me why subtotal for year 2021 in matrix can break arbitrarily shaped filter:
(DimTime[CalendarYear] = 2001 && (DimTime[MonthName] = "July" || DimTime[MonthName] = "August"))
|| (DimTime[CalendarYear] = 2002 && (DimTime[MonthName] = "September" || DimTime[MonthName] = "October"))to this
DimTime[CalendarYear] = 2001 && (DimTime[MonthName] = "July" || DimTime[MonthName] = "August"
|| DimTime[MonthName] = "September" || DimTime[MonthName] = "October")Why original filter context that flows in CALCULATE is not
(DimTime[CalendarYear] = 2001 && (DimTime[MonthName] = "July" || DimTime[MonthName] = "August"))
