VAR vs Measure = Blanks everywhere!

[okusai3000]

Hello everyone,

I'm trying to get working a measure where I apply a filter based on the difference between 2 measures :

• ultima_fecha_mes= if(month(today())=SELECTEDVALUE('dim Fecha'[Mes])&&year(today())=SELECTEDVALUE('dim Fecha'[Anyo]),today(),max('dim Fecha'[Fecha]))
• Last_available_date=calculate((MAX('fact Visitas'[Fecha])), filter(all('fact Visitas'[Fecha]),('fact Visitas'[Fecha])<=max('dim Fecha'[Fecha])),(ALL('dim Fecha')))

The final measure looks like this, and it works indeed:

CALCULATE(DISTINCTCOUNT('fact Prescripciones'[Id Prescripcion]),filter(ALLSELECTED('fact Prescripciones'[Id Prescripcion]),DATEDIFF([ultima_fecha_mes],[Last_available_date],DAY)>2)

But trying to reduce the ammount of measures on my model, I wanted to include the other 2 measures using just VAR:

VAR Last_available_date = calculate((MAX('fact Visitas'[Fecha])), filter(all('fact Visitas'[Fecha]),('fact Visitas'[Fecha])<=max('dim Fecha'[Fecha])),(ALL('dim Fecha')))

VAR ultima_fecha_mes = if(month(today())=SELECTEDVALUE('dim Fecha'[Mes])&&year(today())=SELECTEDVALUE('dim Fecha'[Anyo]),today(),max('dim Fecha'[Fecha]))

RETURN

CALCULATE(DISTINCTCOUNT('fact Prescripciones'[Id Prescripcion]),filter(ALLSELECTED('fact Prescripciones'[Id Prescripcion]),DATEDIFF(ultima_fecha_mes,Last_available_date,DAY)>2)

)

The problem is that with this way, I get BLANK.

Any idea what could be possibly wrong?

thanks!

[AlB · Accepted solution]

Hi @okusai3000 

Variables in DAX are immutable once they are assigned a value at declaration. In your example,

VAR Last_available_date is evaluated before the  FILTER(ALLSELECTED(.... )) and the value obtained stored.

When used within the FILTER(...) Last_available_date will always provide that same result in each iteration. The measure, on the contrary, is calculated anew for each row as the FILTER( ...), with context transition doing its crucial work.

You need either the measure or the full code

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.