Context transition in measures

Anything related to PowerPivot and DAX Formuale
Marc1988
Posts: 7
Joined: Mon Jan 08, 2018 6:11 pm

Context transition in measures

Postby Marc1988 » Mon Jan 08, 2018 7:47 pm

Hi PowerPivotForum

Im trying to study some behaviors about DAX context transition in measures. I believe I understand this topic but sometimes I just get caught out by DAX and I have no idea why its doing what its doing. Today I just want to clarify a very simple example when applying further filters to calculate during context transition.

For the case of simplicity I have a very simple model containing 2 tables, Sales and Product. Sales acts as the fact table and Product as the lookup table. Sales has two columns, ProductKey and Amount. Product has also two columns, ProductKey and ProductName and my model only has three products: Bike, Seat, Wheel.

Lets dig into some examples and please note that the measures are only for educational purposes (They probably dont make sence in a real business-case) :

Say i want to know my total sales so I compute this very simple formula: how much Sales I had on bikes. I compute the following formula to obtain this result:

SumOfSales := Sum(Sales[Amount])


Now i want to know how much Sales I had on bikes. I compute the following formula to obtain this result:


SalesBikes := Calculate( [SumOfSales]; Product[Name]="Bike")


I put both measures in a simple pivot (Pivot 1 attachment) with my Products on the rows and I get exactly what I would expect. My total sales for each product in measure SumOfSales and Sales for Bikes on all rows in my SalesBikes since calculate is overwriting the initial filter context in the example.

Image

Now lets say I make a new measure where I want to retreive sales for Seats. I decide to try to compute it in another way - this time by applying context transition. Im smart so I think that I can: for each row in the Product table I want to compute [SalesBikes] but this time I will add further filters to calculate since I know that during context transition the transition is only applied for the first argument of Calculate. That means that the filter parameters of calculate are applied after transition and these filters might overwrite/change the context genereated by context transition. Great, then I can just overwrite the value of productName in the filter parameter since they are applied afterwards. So I compute this measure to test it:

ContextTransition := SUMX ( Product; Calculate( [SalesBikes]; Product[Name]="Seat") )

I put it in a pivot simply expecting it to overwrite my initial filter on bikes. The result is not what i expected (See pivot 2 attachment)

Image

The result is still the same of bikes as had i just written this: Calculate ( [SalesBikes]; ProductName="Seat"). I actually expected to turn around the evaluation order because i applied context transition but I didnt get what i hoped for. However, it looks like Calculate evaluated it another context though. It computes the formula and tries to put it in a logical AND with the initial context leaving cells blank that do not have Bike in the rows. The result is similar to writing this:

:= Calculate ( [ SumOfSales] ; Filter ( Product; Product[Name] = "Bike" ) )

So my questions are these:

1. What is the reason for this behavior in calculate? Why is it not overwriting the filter on Bikes in my measure [ContextTransition] ?
2. Are there any rules for when calculate is using intersect / overwrite during context transition in measures?

Hoping for your best answers.
Thanks in advance.

Br,
Marc
Attachments
Pivot2.PNG
Pivot2.PNG (6.35 KiB) Viewed 1719 times
Pivot1.PNG
Pivot1.PNG (4.76 KiB) Viewed 1719 times

MattAllington
Posts: 962
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Context transition in measures

Postby MattAllington » Tue Jan 09, 2018 8:45 am

Hi Marc

Very good, deep questions and very well "asked". It shows your DAX skill is strong and you will only get stronger.

can you please post your sample workbook. I will then write a response as a blog post and let you know when it is done.

Matt
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/

P3Tom
Posts: 6
Joined: Tue Jan 09, 2018 4:20 pm

Re: Context transition in measures

Postby P3Tom » Tue Jan 09, 2018 5:11 pm

Marc,

A thoughtful question!

You may or may not know that measures are automatically wrapped inside of a CALCULATE (see page 109, Learn to Write DAX). So the DAX engine unpacks your "context transition" formula as:

Context Transition :=
CALCULATE (
CALCULATE (
CALCULATE (
CALCULATE ( SUM ( Sales[Amount] ); Product[Name] = "Bike" ) )
);
Product[Name] = "Seat"
)

Your product name filter for seat is applied in the outermost CALCULATE. Since filters are applied before expression is evaluated, your product name filter for seat is overwritten by the next internal CALCULATE.

So, from my point of view, the issue here is not context transition, but rather timing (begin with the outermost CALCULATE and work inwards, and for each CALCULATE apply filters first, then evaluate expression).

Matt can say for certain, but I do not think that context is occurring at all: SUM () is not an iterator like SUMX().

If I could make a recommendation: if you do not have a copy, I think you would thoroughly enjoy Learn How to Write DAX.

Tom

Marc1988
Posts: 7
Joined: Mon Jan 08, 2018 6:11 pm

Re: Context transition in measures

Postby Marc1988 » Tue Jan 09, 2018 6:12 pm

Hi Matt

Thank you very much for your reply. My workbook is below. Dont pay attention to that numbers are not the same anymore - the construction is.

I would love to read your blogpost on this topic. Thank you very much. Please see my comment below on P3Tom´s comment as he is also stating what I didnt write about in this post but would like to clarify as well.

Br
Attachments
Test.xlsx
(166.77 KiB) Downloaded 45 times

Marc1988
Posts: 7
Joined: Mon Jan 08, 2018 6:11 pm

Re: Context transition in measures

Postby Marc1988 » Tue Jan 09, 2018 6:28 pm

Hi P3Tom

Thank you very much for your reply.

First, I agree with everything that you say about the evaluation order of calculate and especially the the outermost filter parameter is evaluated first in nested calculates. However as far as my understanding goes this evaluation order gets turned on its head whenever (and only) when we talk about calculate and context transition. In those cases calculate applies context transition first (It does not evaluate the filter parameters first as it usually does) but it only occurs for the first expression of calculate putting a filter on all the columns of the table parameter parsed in the table expression of SUMX. Only later the parameters of calculate are evaluated and they might overwrite the context generated by context transition.

So to wrap it up (as far as my understanding goes) this actually turns your measure on its head so that the filter on Bike becomes the outermost filter to calculate. But again im clearly misunderstanding something here as it is not returning what i expect or maybe there is a hidden complexity about context transition that i have missed. Please elaborate if you understand this topic differently.

Thanks

P3Tom
Posts: 6
Joined: Tue Jan 09, 2018 4:20 pm

Re: Context transition in measures

Postby P3Tom » Wed Jan 10, 2018 3:23 am

Marc,

I refer you to page 117 of the Definitive Guide to DAX, by Marco Russo and Alberto Ferrari (authorities of the DAX language both outside and inside of Microsoft):

"There is an order of precedence between the filter context created by context transition and the filter context created by the conditions of CALCULATE. CALCULATE executes context transition before, and it applies the filters later. Thus any of the conditions of CALCULATE can override the filter created by the context transition."

Tom

Marc1988
Posts: 7
Joined: Mon Jan 08, 2018 6:11 pm

Re: Context transition in measures

Postby Marc1988 » Wed Jan 10, 2018 5:34 am

Tom

Yes I read that but i only see that at just confirms what i wrote previously. Dont you agree?

Marc1988
Posts: 7
Joined: Mon Jan 08, 2018 6:11 pm

Re: Context transition in measures

Postby Marc1988 » Wed Jan 10, 2018 11:18 pm

Hi Matt and Tom

Marco Russo released this blog post a couple of days ago as had he almost been reading along with this post all the time. Think I got it covered now - I would still love to read your post though, Matt.

https://www.sqlbi.com/articles/context- ... ign=1-2018

Br,

MattAllington
Posts: 962
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Context transition in measures

Postby MattAllington » Thu Jan 11, 2018 6:55 am

Yes I saw it too, and almost posted it here too
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/

matthew
Posts: 14
Joined: Sun Apr 17, 2016 5:01 pm

Re: Context transition in measures

Postby matthew » Wed Jan 17, 2018 12:46 pm

I posted this in the comments of Marco’s post too, but I find this snippet from Jeffrey Wang article helpful:

Calculate function performs the following operations:
1. Create a new filter context by cloning the existing one.
2. Move current rows in the row context to the new filter context one by one and apply blocking semantics against all previous tables.
3. Evaluate each setfilter argument in the old filter context and then add setfilter tables to the new filter context one by one and apply blocking semantics against all tables that exist in the new filter context before the first setfilter table is added.
4. Evaluate the first argument in the newly constructed filter context.

With nested CALCULATE it can be challenging to trace, but these steps work for me.
http://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 4 guests

cron