I was watching the following video, and i saw something which identified something which confuses me with DAX.
In that video is the following DAX calculation
Cumulative Sales For High Margin Customers =
CALCULATE( [Profits For High Margin Customers],
Dates[Date] <= MAX( Dates[Date] ) ) )
Filter replaces the Date table (which filters the Sales table). It removes the row context e.g. 25th Feb 20 and replaces it with all dates between 12th Dec 18 and 17th Oct 20 (those currently selected). The Dates[Date] <= MAX(Dates[Date]) further filters that time horizon to 12th Dec 2018 to 25th Feb 2020.
My issue is that the Dates[Date] is for the left side are the Date in the virtual ALLSELECTED(Dates) table, whilst Max(Dates[Date]) is equal to the row context e.g. 25th Feb 20.
Therefore using Dates[Date] refers to two different things, how do I know which is which. Does it depend on whether its on the left side or right side of the <= .
The key thing to keep in mind in breaking down this formula is that FILTER is an iterating function, so it will step through the virtual table defined by the ALLSELECTED function row by row. The MAX( Dates[Date]) will define the stopping point for each row by row sum from the min date each time to the incrementally advancing stopping point.
I’m away from my computer at the moment, but I recall in the Power BI Accelerator series in the Portal (weeks 2 and 3) in the slides and the lectures, we deconstruct the context and iterator issues around this example in a way that you may find helpful.
Keeping in mind that: FILTER is an iterator, and the result of FILTER is a table, here’s how the iteration works.
Iteration 1: Starts with first date in physical table filtered through the ALLSELECTED context. Thus, MAX( Dates[Date] will be 12/12/18 and the cumulative total will be calculated for 12/12/18, the only date in the current filter context <= the MAX date.
Iteration 2: MAX(Dates[Date] will now be 12/13/18. The dates meeting the filter condition <= MAX date are now 12/12/18and 12/13/18, and the cumulative total will be calculated over this two row table.
Iteration 3: MAX(Dates[Date] now 12/14/18, wiith 3 dates in the current context meeting the filter condition
And all the way down the line to the final date in the ALLSELECTED context.
I guess my point is that the logic to do that uses:
Dates[Date] <= MAX(Dates[Date])
So on the left side Dates[Date] is referring to the virtual table, however the right side Dates[Date] is referring to the filter context. So how do i know when Dates[Date] refers to my filter condition and when Dates[Date] refers to my virtual table?
I think the disconnect here may be that we are defining “filter context” differently. When I refer to filter context, it is the combined context generated by the visual, slicers, the filter pane and DAX.
thus, in both cases (right hand side and left-hand side), Dates[Date] refers to the field from the physical table, but within the ENTIRE evaluation context, so the calculation will be done on the virtual table as defined by DAX ALLSELECTED function, which in turn is determined by the slicer values.
yes, i should have said row context in my above question. e.g.
So on the left side Dates[Date] is referring to the virtual table, however the right side Dates[Date] (in Max) is impacted by the row context. So how do i know when Dates[Date] refers to the row context and when Dates[Date] refers to my virtual table?
Is there content on E.DNA that you recommend i watch, as i get that it works but i dont get how it works.
No, the virtual table ALLSELECTED( Dates ) is defined by filter context. Filter context does not imply row context and vice versa. It’s the FILTER iterator function that imposes the row context on the virtual table. Thus, both sides of the equation are affected by the row context imposed by FILTER, which calculates via the iterative process I illustrated in post #9 above.
I would suggest watching and working through Accelerator sessions #3 and #4. Within the materials for each of those there are citations to a range of related EDNA videos on related topics pertaining to evaluation context, iterators, etc.
I would invite others to jump in here if they can offer a more intuitive explanation of what’s going on behind the scenes of the DAX code.
I hope that’s helpful, and that the Accelerator content proves useful as well in explaining these concepts. The work you’re doing is absolutely time well spent, since truly understanding how context operates is the key to everything in DAX – once you’ve got that down, everything else is quite straightforward.
@StuartFlint Start by separating things into variables and you will realize that MAX ( ) isn’t related to the rows iterated by FILTER.
Unless you are using RELATED/RELATEDTABLE/CALCULATE/CALCULATETABLE and time intelligence functions instead of MAX, you can always assume that MAX/MAXX/SUM/SUMX/MINX/AVERAGEX all are evaluated in filter context and not row context.
MAX is getting Filtered here. Just remember this Row context iterates and Filter context filters. The opposite(Filter context iterates and row context filters) of this isn’t true.