Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
And the data shown up is what I expected.
However, the 2nd formula I use shows that context transition doesn't happen. The formula is
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.
Could anyone guide me why the 2nd formula I use didn't work?
Solved! Go to Solution.
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]))
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]))
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |