cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HungFan
New Member

Regarding to the concept of context transition

Hi there,
I would like to ask a question about context transition, the problem I met is described as follows,
In my data model, Table 'Sales' and 'Product' has a Many to one relationship through [Product ID]. I want to add a calculated column in 'Product' to get the [ProductWithSalesQuantity] by means of context transition. The 1st formula I use is 

ProductWithSalesQuantity = CALCULATE(SUMX(Sales,'Sales'[Quantity]))

And the data shown up is what I expected.
ProductWithSalesQuantity.jpg

 

However, the 2nd formula I use shows that context transition doesn't happen. The formula is 

ProductWithSalesQuantity V1 = SUMX('Sales',CALCULATE(SUM('Sales'[Quantity])))
Which I think by using the iterator of SUMX, CALCULATE shall trigger context transtion.

ProductWithSalesQuantity V1.jpg

 

And then I try to add a Table as follows. By using the iterator of ADDCOLUMNS, CALCULATE does trigger context transition. The result is what I expected.

Tb_ProductWithSalesQuantity.jpg

Could anyone guide me why the 2nd formula I use didn't work?

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @HungFan 

Aactually the context transition happens but inside SUMX which is iterating over the sales table. The code is actually returning SUMX ( Sales, Sales[Quantity] ). Using CALCULATE is turning the row context of the SALES TABLE in to a filter context. In other words it is doing nothing. You should be iterating over Products table in order to see the effect of context transition such as SUMX('Products',CALCULATE(SUM('Sales'[Quantity]))) however this iteration is not even required as you are already in the products table row context created by the calculated column itself. So you need only CALCULATE(SUM('Sales'[Quantity]))

 

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @HungFan 

Aactually the context transition happens but inside SUMX which is iterating over the sales table. The code is actually returning SUMX ( Sales, Sales[Quantity] ). Using CALCULATE is turning the row context of the SALES TABLE in to a filter context. In other words it is doing nothing. You should be iterating over Products table in order to see the effect of context transition such as SUMX('Products',CALCULATE(SUM('Sales'[Quantity]))) however this iteration is not even required as you are already in the products table row context created by the calculated column itself. So you need only CALCULATE(SUM('Sales'[Quantity]))

 

Thanks @tamerj1 , I will make some test again. 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors