Evaluation context - mixed filter and row context

[Strom]

Hi, I have a question about how exactly works the evaluation context in example below.

There is a simple model with one fact table Sales without corresponding dimensional tables. 

Now I try to create calculated column Price-CalcCol and one measure Price-Measure:

Price-CalcCol = CALCULATE( SUM( Sales[Price]),  FILTER(Sales,  Sales[Price] >100))

Price-Measure = CALCULATE( SUM( Sales[Price]),  FILTER(Sales,  Sales[Price] >100))

Then I create a simple visual – table and the result is this (Celkem means Total):

Please check if my thinking is correct.

1) Values produced by a measure Price-Measure:

In filter argument of CALCULATE, FILTER(Sales,  Sales[Price] >100)), the Sales table is filtered in current filter context of visual. For example for second line of visual, the filter context that filters the Sales table is {Product:”B”, Price:200}. In next step the expression FILTER(Sales,  Sales[Price] >100) is evaluated to this one line table {Product:”A”, Price:200}.  Then context transition happens and new additional filter argument of CALCULATE is created. Its value is again one  row: {Product:”B”, Price:200}. At the end, the final evaluation context of calculate for second row of visual is on line table {Product:”A”, Price:200}.

2) Values produced by calculated column Price-CalcCol:

Because Price-CalcCol is calculated column, context transition doesn’t affect the Sales table in FILTER argument of CALCULATE, so the result of FILTER(Sales,  Sales[Price] >100)) is two lines table   [{Product:”B”, Price:200},{Product:”C”, Price:300}]. Then context transition happens and new additional filter argument of CALCULATE is created. Its value in the second row of visual is: {Product:”B”, Price:200}. So in CALCULATE, there are two filters [{Product:”B”, Price:200},{Product:”C”, Price:300}] and {Product:”B”, Price:200}. And now, I don’t know what happens. It looks like the filter of context transition is overwritten by result of FILTER function and not merged by AND operator. But why?

Interesting are several more variants of calculated column with this same result:

3) Price-CalcCol = CALCULATE( SUM( Sales[Price]), KEEPFILTERS( FILTER(Sales, Sales[Price] >100)))

This looks like filter created by context transition is not overwritten by result of FILTER function, what is expected.

4) Price-CalcCol = CALCULATE( SUM( Sales[Price]),  FILTER(RELATEDTABLE(Sales), Sales[Price] >100))

This works because current row context is applied to Sales table in FILTER argument.

5) Price-CalcCol = CALCULATE( SUM( Sales[Price]),  FILTER(VALUES(Sales[Product]), Sales[Price] >100))

This is a little strange because in FILTER(VALUES(Sales[Product]), Sales[Price] >100) the condition Sales[Price] >100 is evaluated in row context, but the base table is not VALUES(Sales[Product]) but current row of original table Sales. So I am not sure what is happening. For example for second row of table Sales, the result of FILTER(VALUES(Sales[Product]), Sales[Price] >100) is {Product:[“A”, “B”, “C”]} because Sales[Price] >100 is always TRUE. Then this filter argument and filter context by context transition are merged {Product:”B”, Price:200} with AND operator and the result is 200. But I am not sure if my thinking is correct and why there is difference in final merging of filter arguments in case 2) and 5).

It is not practical example but it could help with deep understanding of evaluation context. Thanks for your answer.

[bcdobbs]

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

[Strom]

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  😞

[bcdobbs]

Can you share your demo file? 

[Strom]

https://drive.google.com/drive/folders/1B62nGxXgDvgeF4FeTj6egs9eMAKgWE-6?usp=sharing

[bcdobbs]

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.

[bcdobbs]

I'm stumped! Have opened a new thread to get help from higher powers.

See Arbitrary Shaped Sets 

[Strom]

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. 

[bcdobbs · Accepted solution]

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.

[bcdobbs]

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 )

[Strom]

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

https://www.sqlbi.com/articles/keepfilters-a-new-dax-feature-to-correctly-compute-over-arbitrary-sha... 

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"))