Forum Discussion
How expanded tables work
- 8 years ago
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?
- 8 years ago
"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.
Well.....
Terminology matters, and in this particular example no row context transition ever takes place so it doesn't apply.
But generally speaking, like i listed before CALCULATE triggers this 4 step process:
- Create a new filter context by cloning the existing one.
- Move current rows in the row context to the new filter context one by one and apply blocking semantics against all previous tables.
- 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.
- Evaluate the first argument in the newly constructed filter context.
So yes, step 2 happens before step 3 and therefore step 2 may be overidden by step 3. And when you do have row context transition, it only ( potentially ) impacts the first argument to CALCULATE.
I thought step 2 is context transition? If not, what context transition is?
I posted similar question on sqlbi.com and this is what Marco Russo replied:
https://www.sqlbi.com/articles/understanding-context-transition/
Getting even more confused, what context transition is :( ...