Forum Discussion
How expanded tables work
Hi experts,
I have been experimenting with expanded tabels and came up to this strange behaviour, which hopefully somebody can explain. I built two simple tables:
They are related via the ProductID field. To my best understanding expanded table looks like this:
Now I've build a couple of measures to sum Amount by ProductID. However I get different results in the following two cases :
Maybe you know why is that? I was always thinking that you can filter on any of the fields of an expanded table and the results should be the same.
Your confusion lies in the fact that even though an expanded table is a series of left-outer joins of tables from many -> 1, the expanded table still has all the columns of all the tables. And tables are a collection of independent columns. Meaning that a filter on one column does not affect another until after applied to the model. (and even then the impact if from cross filtering rows) Take this code here:
Sum Amount = CALCULATE(SUM(t3Sales[Amount]),t3ProductName[ProductID])
internally, it is written as this:
Sum Amount = CALCULATE ( SUM ( t3Sales[Amount] ), FILTER ( ALL ( t3ProductName[ProductID] ), t3ProductName[ProductID] ) )So it will return all the ProductID's from the t3ProductName column. But the table also has a filter on t3Sales[ProductID] because you put it on the rows which when combined filters down the t3Sales table to just where t3Sales[ProductID] = 100 for row 1, and so on for rest of the table. Grand total has no filter on t3Sales[ProductID] and only has the above filter on tsProductName[ProductID] column which returns all values, so you get the correct grand total.
next is your Sum Amount2
Sum Amount2 = CALCULATE(SUM(t3Sales[Amount]),t3Sales[ProductID])
which internally is rewritten as:
Sum Amount2 = CALCULATE ( SUM ( t3Sales[Amount] ), FILTER ( ALL ( t3Sales[ProductID] ), t3Sales[ProductID] ) )So, even though you put t3Sales[ProductID] on the rows, since you also referenced that column as a filter argument to CALCULATE, the table row filter is overridden by the above FILTER statement which returns all ProductID for t3Sales for every row which is why you get the grand total amount for every row in table. Difference from Sum Amount is now there is no filter on the t3ProductName[ProductID].
Make sense?
"Context Transition" refers to moving a column filter from row contex to filter context.
"Modify filter context" means just that. When you have columns on the rows, columns, filter area, slicers of report that is the initial filter context. Either row context transition or arguments to 'CALCULATE' may change ( or modify) that filter context.
16 Replies
I think, the way you used the Sum formula is incorrect. Since, you have already created the relationship with Product ID, you can just drag a table visual then, select ProductID and Amount. Usually, power bi will do the sum based on Product id, if not, just select the Amount under Visualization-->Values then select the Small down arrow and then select Sum.
Sure, I can get away without using measures here. But I want to understand why CALCULATE makes different context transitions on the same expanded table.
Hi gvg,
Based on my test, I found that if we add t3Sales[ProductID] into table visual, and for measure [SumAmount1], we refer to its related table column t3ProductName[ProductID], it will calculate total values grouped by ProductID. However, for measure [SumAmount2], we refer to its own column t3Sales[ProductID], the formula is considered as SumAmount2 = CALCULATE(SUM(t3Sales[Amount]),ALLSELECTED(t3Sales[ProductID])), means regardless of t3Sales[ProductID]. So, in your scenario, you should modify the formula for SumAmount2 to:
SumAmount2 = SUM(t3Sales[Amount])
Best regards,
Yuliana Gu
Well, I have t3Sales[ProductID] in both tables, that I am putting to test. However interestingly, if I change t3Sales[ProductID] to t3ProductName[ProductID] in the last visual, I get correct results. Getting totally confused :(
Here is my test pbix file Expanded table test .
Your confusion lies in the fact that even though an expanded table is a series of left-outer joins of tables from many -> 1, the expanded table still has all the columns of all the tables. And tables are a collection of independent columns. Meaning that a filter on one column does not affect another until after applied to the model. (and even then the impact if from cross filtering rows) Take this code here:
Sum Amount = CALCULATE(SUM(t3Sales[Amount]),t3ProductName[ProductID])
internally, it is written as this:
Sum Amount = CALCULATE ( SUM ( t3Sales[Amount] ), FILTER ( ALL ( t3ProductName[ProductID] ), t3ProductName[ProductID] ) )So it will return all the ProductID's from the t3ProductName column. But the table also has a filter on t3Sales[ProductID] because you put it on the rows which when combined filters down the t3Sales table to just where t3Sales[ProductID] = 100 for row 1, and so on for rest of the table. Grand total has no filter on t3Sales[ProductID] and only has the above filter on tsProductName[ProductID] column which returns all values, so you get the correct grand total.
next is your Sum Amount2
Sum Amount2 = CALCULATE(SUM(t3Sales[Amount]),t3Sales[ProductID])
which internally is rewritten as:
Sum Amount2 = CALCULATE ( SUM ( t3Sales[Amount] ), FILTER ( ALL ( t3Sales[ProductID] ), t3Sales[ProductID] ) )So, even though you put t3Sales[ProductID] on the rows, since you also referenced that column as a filter argument to CALCULATE, the table row filter is overridden by the above FILTER statement which returns all ProductID for t3Sales for every row which is why you get the grand total amount for every row in table. Difference from Sum Amount is now there is no filter on the t3ProductName[ProductID].
Make sense?
