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.
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?
Thanks Strom . Going to have to give this one some thought. I would have thought along the same lines as you! Will get back to you once I've thought it through.
I'm stumped! Have opened a new thread to get help from higher powers.