please explain why MAX(biorthYear) works but measure version doesnt

[ilcaa722]

hello, learning DAX and need to understand WHY one works but not other...

i have a [BirthDate] column and I created a calculated Column [BirthYEAR].  I did this to filter my [total customers] born before [BirthYEAR] so user can select a birthYear and it will show a total customers born before this year.  

this code works with slicer based on [BirthYear] column (birthYear is not in pivot table)

total customers born before :=
CALCULATE([total customers],
FILTER( ALL(Customers),
   Customers[birthYear] <= max(Customers[birthYear]) ))

BUT this code does NOT,

create sperate measure & use in another measure as a value. year_slicer:= MAX([BirthYEAR])

total customers born before :=
CALCULATE([total customers],
  FILTER( ALL(Customers),
      Customers[birthYear] <= [year_slicer] ))

the code that use a [measure] returns the total customers in Table

BUT, if i use a seperate disconnected table as the years and use the 2nd method i posted it work!

i can figure out the logic. a quick explanation would be helpful

[AlexisOlson]

When you call a measure, there is an implicit CALCULATE involved that performs a context transition. Your second code box is equivalent to:

total customers born before :=
CALCULATE (
    [total customers],
    FILTER (
        ALL ( Customers ),
        Customers[birthYear] <= CALCULATE ( MAX ( Customers[birthYear] ) )
    )
)

I recommend reading the following article for more detail. It discusses an example very similar to yours.

https://www.sqlbi.com/articles/understanding-context-transition-in-dax/

[AlB · Accepted solution]

Hi @ilcaa722 

The reason is context transition, a very important concept in DAX.

On your first version  max(Customers[birthYear]) always returns the desired value (that selected in the slicer)

On your second however, context transition kicks in (it always does when there is a calculate or when a measure is used). The result yielded by the measure is the  value for Customers[birthYear] in the row currently under evaluation in FILTER. Therefore the Customers[birthYear] always equals  [year_slicer]  and the condition

Customers[birthYear] <= [year_slicer]

is always true.

Check this out:

https://www.sqlbi.com/articles/understanding-context-transition/

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.