Forum Discussion
Solved
Losing context transition by using filter
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 calcu...
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.
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
)
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 )
)
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.