Latest Enterprise DNA Initiatives


The same column meaning two different things in a filter

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],
FILTER( ALLSELECTED(Dates),
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 <= .

1 Like

@StuartFlint ,

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 hope that’s helpful.

  • Brian

It helps but i still dont understand.

I was using the row 25th February 2020 as an example.

My point is that for this row the MAX(Dates[Date]) is 25th Feb 2020, so for this use of Dates[Date] it is from the Dates table.

However the Dates[Date] on the left hand side is populated by the dates in the ALLSELECTED(Dates) virtual table

My confusion is how DAX knows whether the Dates[Date] used is from the physical date table or from the virtual ALLSELECTED(Dates) table.

Does my question make sense?

@StuartFlint MAX ( Dates[Date] ) is used from the Physical table as visible under the current filter context. The date inside MAX isn’t from the current row.

@StuartFlint ,

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.

  • Brian

Hi @StuartFlint , welcome to the forum :slight_smile:

Good to see that you are having progress with your inquiry.

Did the response provided by the users and expert above help you solve your query?

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

Hi @StuartFlint, we’ve noticed that no response has been received from you since Oct 10.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Had a look at that and does mention the formula pattern. But im not sure that it answers the question

@StuartFlint ,

See if this helps:

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.

Hope that’s helpful.

  • Brian

yes, that is absolute how the calc work.

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?

Stuart,

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.

  • Brian

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.

@StuartFlint ,

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.

– Brian

1 Like

@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.

3 Likes

Hi @StuartFlint, we’ve noticed that no response has been received from you since October 16

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @StuartFlint, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, please create a new thread.

@StuartFlint