Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
HungFan
Regular Visitor

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors