Forum Discussion
How expanded tables work
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.
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 :( ...
To be clear: If you put a value on the "Rows" section of a Matrix visual, that is a Filter context, not Row context. Again, row context exists in only two places: In a data table when adding calculated column & programatically by using an iterator function. But it takes another function to trigger transition from row context to filter context.
Here is a simple one column table i did in Excel:
with two calculated columns I wrote:
Max Date = MAX ( Calendar[Date] ) Calculate Max Date = CALCULATE ( MAX ( Calendar[Date] ) )
Row context exists in a data table, but only filter context impacts results. in '[Max Date]' row context for each row value computed is the row's respective date, but since no context transition, filter context is empty and so calculated column returns the max value for entire column which is 1/4/2017.
In '[Calculate Max Date]' we wrap it in a CALCUATE. CALCULATE triggers context transition and removes row's respective date from row context, and adds to filter context. So when the MAX evaluates, there is only one value left which is current row's 'Date' which is what it returns.
For measures, FILTER is a very common iterator. What is difference between these two uses?
FILTER ( ALL ( Calendar ) , Calendar[Date] <= MAX (Calendar[Date] ) ) FILTER ( ALL ( Calendar ) , Calendar[Date] <= LASTDATE (Calendar[Date] ) )
FILTER setups up iteration of entire 'Calendar' table.
The first measure uses 'MAX', which will return the last date value visible in the current filter context which is most likely what you had intended. So row context exists, but no trigger to move it to filter context and there for FILTER will return subset of Calendar table.
But the second measure uses 'LASTDATE' which is a table function that does trigger context transition. So for each row being iterated, LASTDATE will remove the respective date value from row context and add to filter context. Therefore when evalueated, LASTDATE will always return the currently iterated rows' value, which is turn will make the statement always true for entire Calendar table no matter what you have selected in a slicer (or some other means of selecting a date range). Probabaly not what was intended.
So that's row context and how it impacts results...clear? Or even more confusing?
I have read all of Marco & Alberto's articles. They are definitely the go to guys on Dax. And I read your questions in the article and of course Marco is 100% correct. I just think you misunderstood some of his answers.
"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.