topic Re: what's the difference creating the filter as a variable or using inside of the filter ? in DAX Commands and Tips

what's the difference creating the filter as a variable or using inside of the filter ?

brickanalyst — Sat, 30 Mar 2024 19:56:08 GMT

I have seen a question in one of the interviews below :

Let's say we have a dax measure

[Max Date]:= MAX(DatesTable[Date])
then I use it in the following below;

FILTER(ALL(DatesTable), DatesTable[Date] <= [Max Date])

then, I wanted to try this below instead;

FILTER(ALL(DatesTable), DatesTable[Date] <= MAX(DatesTable[Date]))

these two are not giving the same results, explain why these two are acting different?

That is the question, please share with details including videos, doc links if its needed.
thank you !

Re: what's the difference creating the filter as a variable or using inside of the filter ?

kriscoupe — Sat, 30 Mar 2024 20:50:38 GMT

Hey brickanalyst ,

Yeah this is a tricky one. When using the [Max Date] measure in another measure an implicit CALCULATE is wrapped around the function. Since FILTER is an iterator context transition occurs turning each row in the DatesTable into a filter context.

You would achieve the same results if you changed your second formula to read

FILTER(ALL(DatesTable), DatesTable[Date] <= CALCULATE( MAX(DatesTable[Date]) ) )

Check out context transition and implicit CALCULATE around measures for more info.

Hope it helps,

Kris

Re: what's the difference creating the filter as a variable or using inside of the filter ?

brickanalyst — Mon, 01 Apr 2024 00:56:03 GMT

My best guess is when it's defined as variable it's just one value right lets say 3/31/2024 today. and every row in the filter function this date is going to be filtered by 3/31/2024 however when we filter max date within the filter function on number 2 it filters row by row and it gives a different result.

I'm not sure this idea is correct, but also I'm curious when we should use #1 and when we need to use #2

Re: what's the difference creating the filter as a variable or using inside of the filter ?

Anonymous — Mon, 01 Apr 2024 05:36:34 GMT

Hi brickanalyst ,

Thanks kriscoupe for sharing, I have some more suggestions here that I hope will help you.

1. for the use of measure in the filter function. When you use it in a function, Power BI calculates the entire , once, and then this value is always used as a comparison value for each row calculated by the function. You can think of the
Max Date as a constant.

2. For the case of using max directly in the filter function. It is calculated for each row in the context modified by the function itself, which may result in different maximum date values being calculated for different subsets of the data, depending on the context established at each step of the evaluation.

3. If you need a consistent value to compare against all rows in the table, you can use the value stored in the measure, which is useful for global comparisons. If you need the comparison criteria to be adjusted according to the context of each row or subset of rows, you can use max directly in This method can be used for more dynamic calculations, where the maximum value may vary according to other filters or conditions applied to the data. More information on context can be found at the link: Mastering DAX Context: Understanding Row, Filter, and Evaluation Context (rajanand.org)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: what's the difference creating the filter as a variable or using inside of the filter ?

kriscoupe — Mon, 01 Apr 2024 08:06:38 GMT

Hey Anonymous ,

I think you may have got things the wrong way round in your post? When you use the measure you won't get a consistent value to compare against each row. When using a measure in another function an implicit CALCULATE is wrapped around the measure and context transition occurs changing that row context into a filter context. Here's an example

Using MAX directly as a function you will get the same value across every row (the max date in the table). This is because a row has no filter context in this scenario
Using [Max Date] measure you get a date that is equal to the max date in that rows filter context. This is because Power BI actually runs CALCULATE( [Max Date] ) under the hood.
Finally for clarity if you run CALCULATE( MAX( date ) ) then you will get the same result as [Max Date]

This can be seen below. I've used ADDCOLUMNS as an example as it is also an iterator like FILTER

When context transition occurs it changes each row into a filter context. Since my dates are unique the Max Date for each row is equal to the date in the row itself. It may be clearer to see in this example using a simple SUM

Because rows 1 and 2 are duplicates the context of that calculation is "Give me the sales when Product = A and Sales = 200" since 2 rows satisfy the condition you can see we get 400 as a total. If the sales were different and it wasn't a duplicate you would get a different result since "Give me the sales when Product = A and Sales = 200" and "Give me the sales when Product = A and Sales = 300" are different.

brickanalyst this is just scratching the surface but when I started to understand context transition my DAX became so much more powerful. Funny I missed this was an interview question and one I've used in the past. Understanding this shows you have a deep knowledge of evaluation contexts.

Hope it helps to clarify,

Kris

Re: what's the difference creating the filter as a variable or using inside of the filter ?

Anonymous — Mon, 01 Apr 2024 08:44:40 GMT

kriscoupe , you are right. My thoughts are consistent with yours. I am sorry that some problems in my expression have caused you some misunderstandings.

For the first case, Max Date is outside the filter, so all in the filter has no effect on it. For the second case, max is inside the filter, so the range is the entire table.

As shown in the figure below, measure returns the count of each row, while measure2 returns the count of the entire table.

Measure = COUNT('Table'[date])

Measure 2 = COUNTAX(ALL('Table'),'Table'[date])

brickanalyst ,Here is a link that I hope will help you understand the filter context.Understand the Filter Context and How to Control i... - Microsoft Fabric Community

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: what's the difference creating the filter as a variable or using inside of the filter ?

brickanalyst — Sun, 07 Apr 2024 23:00:47 GMT

Thank you so much, It makes sense with examples.