Second example of KEEPFILTERS on www.dax.do

[PB12]

Hello. I don't udnerstand how they get the numbers of [SalesX] in the table visual in the the second example of KEEPFILTERS on www.dax.do? Here is the link: https://dax.do/RvposJvoHWnco0/  Could someone explain? Best regards

[Anonymous]

Hi @PB12 ,

SachinNandanwar and johnt75 have both provided detailed explanations regarding your problem. If you find their responses helpful in understanding how the KEEPFILTERS function works in the test example, please consider marking their replies as the solutions. Thank you.

Best Regards

[PB12]

Hello v-yiruan-msft. How should I proceed if there is no satisfactory explanation to the question?

[SachinNandanwar]

Hi @PB12 ,If you have an understanding of row/filter context only then it would be easy to understand the explaination provided.

Did you get a chance to go through this ?

[PB12]

Hello Sachin. Thank you for taking your time to have a look at this post. Is the number of [SalesX] showing:

-red in 2008

-red in 2007

-white in 2008

-white in 2007

- or a completely different combination. If this is the case, what is the combination then?

Best regards

[SachinNandanwar]

Its showing the [Sales Amount] ignoring the filters ("CY 2008", "Red" ), ( "CY 2007", "White" ).

Lets take example of product color white. As explained by @johnt75 the measure SUMX ( VALUES ( Product[Color] ), [Sales Amount] ) causes the context transition and executes under the filter context and returns the [Sales Amount] for White ignoring the filter for year 2007 which is the original filter.

This happened because the context transition nullified the original filter context and only the current filter context which is White is applied to return the [Sales Amount]. Now to prevent this from happening MEASURE Sales[SalesX Keep] uses KEEPFILTERS that maintains the original filter context i.e. ( "CY 2007", "White" ) for the color white and ("CY 2008", "Red" ) for the color red.

You can view the model here : https://dax.do/RvposJvoHWnco0/diagram/ 

[SachinNandanwar]

So SUMx is an iterator and runs in a row context and causes context transition.In your example,there is a filter on { ( "CY 2008", "Red" ), ( "CY 2007", "White" ) }.

Due to context transition, the measure MEASURE Sales[SalesX] will not respect the filter YearsAndColor as by default any context transition dont respect any existing filters and will be oveeriden .That is the default nature of Context transition i.e to override any existing filters.

But incase you want the filters to respected that were applied before the context transition you have to use KEEPFILTERS.KEEPFILTERS mainatin the existing filters that were applied prior to the context transition

The behavior is not typical only with iterators.You can observe the same behavior with CALCULATE as well.

I created two CALCULATE functions in the same example

  MEASURE Sales[SalesC] =
        CALCULATE ([Sales Amount],VALUES('Product'[Color]))
    MEASURE Sales[SalesC Keep] =
        CALCULATE ([Sales Amount],KEEPFILTERS(VALUES('Product'[Color])))

 and used KEEPFILTRS with CALCULATE in one of them to respect the existing filters prior to the context transition. CALCULATE itself causes the context transition.

I have arrowed the two CALCULATE functions I created and you can see, they also project the same behavior like the iterator SUMX

P.S : I hope I am correct in this explaination and other DAX gurus on this forum could correct me in my understanding.

[johnt75]

@SachinNandanwar your explanation is mostly correct, but the iterator SUMX does not by itself cause context transition. Calling the [Sales Amount] measure is what causes the context transition, that happens any time a measure is called.

[SachinNandanwar]

@johnt75 .Oh...I always used do think that SUMX by its inherent nature causes context transition.This is because as SUMX is an iterator and it creates a row context when it iterates across each row and the context then gets converted to a filter context when evaluating the expression in SUMX.

I did check the dax guide https://dax.guide and it confirms your claim

I have one follow up question on this.
In the example [Sales Amount] is a measure used in the SUMX function and what if it is replaced by a column .Will it not cause a context transition  ? If it does not then how would it aggregate across unique sets/groups in the data.

[johnt75]

If [Sales Amount] were replaced by a column reference then it would not cause context transition, and the calculation would happen in the row context only. If we take the definition of [Sales Amount] itself

Sales Amount =
SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

Because there is no call to CALCULATE, and no measure reference, then context transition does not happen. So [Sales Amount] iterates over all rows in Sales which are visible in the current filter context, and for each row it performs the multiplication.

Perhaps a better example is ADDCOLUMNS. ADDCOLUMNS is an iterator, like SUMX, but it does not by itself force context transition. If you execute 

EVALUATE
ADDCOLUMNS (
    SUMMARIZE ( Sales, Product[Brand], Product[Color] ),
    "@sales", SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
)

you will see all combinations of brand and colour, but they all produce exactly the same number in [@sales] because the row context from ADDCOLUMNS is not converted into a filter context.

If you wrap the SUMX inside CALCULATE then context transition is performed and you get the numbers you would expect.

[SachinNandanwar]

Thank you  @johnt75   very much for the clarifications .They have been an eye opener.I now understand that as the [Sales Amount] is a measure, it causes the context transition for SUMX and the context transition does not happen because of SUMX itself.

This is my final follow up question and I promise I wont bug you anymore 🙂

Lets take this mesaure.

MEASURE Sales = SUMX ( VALUES ( Product[Color] ), [Sales Amount] )

Now in case I change the measure to something like this just for sake of the discussion

MEASURE Sales = SUMX ( VALUES ( Product[Color] ),SUM(Sales[Quantity]))

will it cause a context transition ? AFAIK(I may be wrong), aggregate functions execute under the current filter context. So what will be the context here ? given that iterators execute in row context and aggregate functions executes in filter context.I understand that aggregate function themselves dont cause context transition.

and if I create a seperate measure for SUM(Sales[Quantity]) say SUM_QTY and use it in the measure something like MEASURE Sales = SUMX ( VALUES ( Product[Color] ),SUM_QTY),will this cause a context transition.I would assume it would..

Is it fair to say that any measure that uses a function(aggregate or time intelligence)if invoked in an interator will cause a context transition.

And I tried your ADDCOLUMNS example and you were spot on.

EVALUATE
ADDCOLUMNS (
SUMMARIZE ( Sales, Product[Brand], Product[Color] ),
"@sales", SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
)

[johnt75]

If you change the measure to

MEASURE Sales = SUMX ( VALUES ( Product[Color] ),SUM(Sales[Quantity]))

then there will be no context transition for the SUM. 

When the measure itself was invoked there would be context transition, which could affect which values of Product[Color] were visible. The SUMX would then iterate over those visible values, but that iteration wouldn't really matter as there is no CALCULATE to force context transition.

If you create a measure like 

MEASURE Sales = SUMX ( VALUES ( Product[Color] ),[SUM_QTY])

then there would be context transition so you would get the sum for the appropriate colour.

Whenever a measure is called there is always an implicit CALCULATE to force context transition. This is independent of what the measure does, whether it uses an aggregator or not. There is always a CALCULATE.

If you call an aggregator rather than a measure, then there is never context transition unless you explicitly call CALCULATE.

[SachinNandanwar]

Thank you very much @johnt75 for your time and the detailed explaination.

Greatly appreciate it..