Losing context transition by using filter

[pankajk83]

Hi All,

Struggling to understand this behaviour of calculate. Newbie here. I have two tables. A sales table and a product table. I am creating a calculated column inside product table to to calculate sales of each product only when the quantity sold (in sales table) is greater than 1. 

In the screenshot, you can see that Calculate doesn't do context transition when using a filter inside. However, if I write the formula as

single calculate=calculate(sum(Sales[pk_total sales amount]),Sales[Quantity]>1) , it enforces this context transition. 

I am also beginning to wonder if this above new formula is deceiving my logic. Isn't the above new formula for single calculate the same as 

single calculate = calculate(sum(pk_total sales amount), filter(all(sales[quantity), sales[quantity)>1). If yes, then context transition must not have been obeyed in the first place!

It also obeys context transition if I use calculate twice . example 

Double calculate = calculate(calculate(sum(Sales[pk_total sales amount]), filter(sales,Sales[Quantity]>1)))

[tamerj1]

Hi @pankajk83 

The reason is very simple. CACULATE imposes context transition over the first Argument (the expression). However, the 2nd argument (filter argument) of CALCULATE is evaluated in the outer filter context. But guess what! There is no outer filter context in this case. It's a calculated column with only row context. 
When you wrap the whole formula with another CALCULATE then the inner calculate in its totality becomes the expression argument of the outer CALCULATE. Therefore, context transition is applied and hence "FILTER ( 'Table'..." becomes "FILTER ( CALCULATETABLE ( 'Table' )..."

[pankajk83]

Thank you. However, why does the same formula obey context transition when I use the ALL function

sing calc = calculate(sum(Sales[pk_total sales amount]), filter(all(Sales[Quantity]), Sales[Quantity]>1))

I have taken a look at the other answers and these are great insights. I am still trying to get some clarity.

I completely agree with what you have written here in your answer, but should it not be the same when you use Filter(ALL(sales[quantity),.....) just as it is for Filter(sales,........)

[tamerj1]

@pankajk83 
Yes that is becuase ALL(Sales[Quantity]) removes the filter from only that column. I would also say that FILTER ( VALUES ( Sales[Quantity] ),... would also give the same result. 
Accordingly, without the outer filter context FILTER ( Sales,... is equivalent to FILTER ( ALL ( Sales ),...

[pankajk83]

Apologies that I might be dragging this a little too far, but you see, using ALL, as you said, removes the filter from only that column. However, I am not doing this in a visual but in a calculated column. In thet case, there is no filter context at all when filter is first evaluated. So I am confused why does the presence of ALL make any difference. 

[tamerj1]

@pankajk83 
As I mentioned in my previous reply, there is absolutely no difference, in this case, between using FILTER - ALL or FILTER - VALUES. In fact VALUES, DISTINCT, ALLSECTED will all perform as ALL because the is no outer filter context.
What you are missing here is that the filter argument of CALCULATE replaces the filter context created by CALCULATE. When placed in a calculated column, CALCULATE converts all the columns of the current row of the table into a newly created filter context. When you use FILTER - Table you actually replace that filter context with a new one. Except this time the new filter context includes all the rows, not only the current row. That is the very same effect of FILTER - ALL ( Table ).
When you use FILTER - ALL ( Table[Column] ) you only replace that column in the filter context created by CALCULATE but the rest of that filter remains.

My recommendation, avoid using CALCULATE in creating calculated columns but rather try to rely on a NoCALCULATE approach.

[AntrikshSharma]

@pankajk83It all has to do with CALCULATE's evaluation order and Expanded Tables.

Single CALCULATE:

CALCULATE (
    SUM ( Sales[pk_total sales amount] ),
    Sales[Quantity] > 1
)

The first thing that happens here is evaluation of Sales[Quantity] > 1 which becomes FILTER ( ALL ( Sales[Quantity] ), Sales[Quantity] > 1 ) which is evaluated in the external filter context that is whatever is outside CALCULATE. This argument could have been something like FILTER ( VALUES ( Sales[Quantity] ), Sales[Quantity] > 1 ) therefore it is first evaluated in the external filter context, and since in a Calculated Column there is no external filter context when this argument is evaluated it isn't affected by anything from the other columns.

After this Context Transition happens and all the values of all the columns(even the calculated ones) of each row are transformed into equivalent filter context, this will be applied in intersection with the Sales[Quantity] > 1

If there are any CALCULATE modifier such as USERELATIONSHIP, CROSSFILTER, ALL, REMOVEFILTERS, ALLEXCEPT, ALLSELECTED they are applied next so that they can modify the effect of Context Transition and NOT THE FILTER PREPARED IN THE FIRST STEP i.e. Sales[Quantity] > 1

After this whatever remains in Filter Context from context transition and filter arguments is applied to the first argument of CALCULATE and then the measure/table is evaluated. Please note if required the First step will overwrite the Context Transition, Context Transition can't overwrite the Filter arguments of CALCULATE unless code is altered specifically.

This is why your calculation works fine.

Single CALCULATE with Expanded Sales:

CALCULATE (

    SUM ( Sales[pk_total sales amount] ),
    FILTER ( Sales, Sales[Quantity] > 1 )
)

Here table expansion comes into picture, the reference to Sales is not just Sales table alone but all other tables that can be reached from Sales following a Many : 1 relationship, example: products, customers, dates, a FILTER on Sales here will filter all the other Dimension tables as well.

Context Transition by default in a single CALCULATE affects only the first argument of CALCULATE, here it doesn't affect FILTER ( Sales... part of the code.

In this example, first whole model(sales, dimension1, dimension2, dimensionN) is filtered for Sales[Quantity] > 1 and kept aside, then context transition happens, but after context transition the result of FILTER ( Sales... is applied which overwrites the effect of context transition.

Since you're applying only one filter to Sales and then applying that to the first argument of CALCULATE you get grand total sales that only considers Quantity > 1

Double CALCULATE:

CALCULATE (

    CALCULATE (

        SUM ( Sales[pk_total sales amount] ),

        FILTER ( Sales, Sales[Quantity] > 1 ) 

    )

)

Since you're using 2 CALCULATE and the inner CALCULATE is the first argument of outer CALCULATE when the outer CALCULATE initiates context transition it will filter the FILTER ( Sales... part that's why you will see the correct result because Sales is filtered for appropriate rows by context transition + Sales[Quantity] > 1

In this case the steps are:

Outer CALCULATE initiates Context Transition and invalidates any row context, no more row context are available for context transition after this.

Inner CALCULATE will first evaluate FILTER ( Sales ... in the external filter context, what is it? the row context transformed to equivalent filter context by outer CALCULATE.

Inner CALCULATE can't perform context transition because there aren't any row context available as outer CALCULATE invalidated them.

CALCULATE will apply FILTER ( Sales.. to the first argument and evaluate the measure.

This is why you shouldn't reference the full table in DAX, not only are they hard to understand but they also impact the performance adversely and can result in calculations that are incorrect.

[tharunkumarRTK]

@pankajk83 

Before reading my answer, I would suggest you to go through these two blogs
https://www.sqlbi.com/articles/expanded-tables-in-dax/
https://xxlbi.com/blog/power-bi-antipatterns-9/

https://xxlbi.com/blog/power-bi-antipatterns-10/

I would like to answer your question in two parts, first lets see the difference between, filter function in calculate and predicate filter in calculate.

-- filter predicate in calculate

single calculate=calculate(sum(Sales[pk_total sales amount]),Sales[Quantity]>1)           

when you are filtering on a column within calculate as filter predicate that means you are filtering on the expanded table (which includes columns from sales and product).

The equivalent of above DAX expression is:

single calculate=calculate(sum(Sales[pk_total sales amount]), Filter(all(Sales),Sales[Quantity]>1) )    

However, with the below syntax you are filtering on sales table only.

single calculate=calculate(sum(Sales[pk_total sales amount]), Filter(Sales,Sales[Quantity]>1) )      

Now coming to the next part, first of all 

One of the rule for calculate function is, calculate fitler arguments are evaluated in original row and filter context (i.e row context in your scenario) and the result of these arguments then effect the filters created due to context transition. 

with filter predicate (Sales[Quantity] > 1) or (Filter(all(sales), sales[quantity] >1):

• The resulting filter context includes columns from both the Sales and Product tables, and filters produced due to context transition from the Product table will affect the result.
• The CALCULATE function evaluates its expression within this modified filter context, taking into account any filters applied by the FILTER and ALL functions.

with double calculate with filter function:

• Here, a FILTER function is nested inside a CALCULATE function to filter the Sales table based on the quantity column.
• The inner CALCULATE evaluates its expression within the filter context created by the FILTER function, which filters the Sales table.
• The outer CALCULATE evaluates the result of the inner CALCULATE within its own filter context, potentially affecting the result through context transition.

with Filter(Sales, Sales[Quantity] > 1)

• Here also the filter argument is evaluated in original filter context but the result will not contain the columns from product table (not an expanded version of sales)
• So, though the filters due context transition are not observable)

Note: I am not an expert in the core concepts of DAX. I wrote the above answer based on my understanding, so take it with a grain of salt.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[TRIXTERBINOOB]

correct me if I'm wrong but guessing:

Are you using CALCULATE() to make a calculated column? If that is the case that might be causing you trouble. Let me explain more about the logic:

single calculate makes the SUM() over the table, because there is no context transition at all. the measure would be working fine in a matrix if it's calculated in a measure, not in a column.

I don't know why the other two are working fine, but my theory is that ALL() is forcing to make the calculate in every different row.

It would be very useful to us you share the pbix file or your data to run some tests.

Hope been useful. 

PD: sorry about my English still learning.