Hello Community,
given:
Measure Max Date = Max(Date[Date])
Filter 1 = Filter(All(Date), Date[Date]<=[Max Date] )
Filter 2 = Filter(All(Date), Date[Date]<=Max(Date[Date]) )
I would like to understand why measures Filter 1 and Filter 2 will produce different results. It’s a hypothetical question, I don’t really have a pbix .
I’m a bit puzzled as measure branching is the best practice.
Thank you
Hi @fudzen88, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.
A perfect initial question includes all of the following:
Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions
Not completing your data may sometimes cause delay in getting an answer.
There is no pbix file. This DAX question is rather conceptual.
Hi,
have you checked the result of your above query on which scenario it appears different .
If you can throw some light where you find these two giving different result then i can help you efficiently.
Thanks,
Anurag
@fudzen88 When you use the measure branching technique you are calling a measure in the row context inside FILTER, a measure always has an invisible CALCULATE wrapped around it so the code that you think is same isn’t actually same, your first code looks something like this to the DAX Engine:
FILTER (
ALL ( Dates ),
Dates[Date] <= CALCULATE ( MAX ( Dates[Date] ) )
)
Calculate converts each row into Filter Context that defines what MAX would return. For each cell of the below visual, FILTER iterates ALL dates and then for each row iterated, CALCULATE performs context transition and shifts the currently iterated date in the row context to an equivalent filter context, once the calculation for one cell of the report is complete CALCULATE + MAX always return the last date, and then I get the COUNT which is the same as the number of rows in the Dates table.
But in the second code there is no CALCULATE around MAX, so MAX is getting filtered by the filter context that is created by the report so for each month I get Current Month + COUNT of all the previous months.
Moral of the short story, don’t create measures for code that you are going to use for evaluating or filtering tables. Always split the code into variables so that you won’t encounter hidden calculate. For example your First code will work if you store the [Max Date] in a variable and since variables are just constants it won’t invoke context transition.
Spectacular explanation. I actually started answering this question yesterday and stopped specifically because I wanted to see your answer. Was not disappointed. Thanks!
@AntrikshSharma thank you so much. I had a general understanding of the difference. You went beyond and explained it vey well in plain English )).
