Forum Discussion
Help with Simple DAX formula
Try storing the MEASURE value in a variable first
Revenue = VAR myMEASURE = [MonthYear] RETURN CALCULATE ( SUM ( fact[Revenue] ), FILTER ( fact, fact[fiscalMonth] = myMEASURE ) )
I am doing something pretty straightforward.
My first "calculated measure" "MonthYear" returns a value say = "December 2018"
I use this calculated measure "MonthYear" in a different calculated measure as a Filter parameter -
"Revenue" = CALCULATE(SUM(Revenue), FILTER("fact", fact.fiscalMonth = MonthYear))
I can verify that MonthYear measure actually returns "December 2018"
But Revenue measure returns a blank value.
When I replace the MonthYear parameter , with a hard coded value
"Revenue" = CALCULATE(SUM(Revenue), FILTER("fact", fact.fiscalMonth = "December 2018" )) - Now this returns proper revenue value.
I do not understand why this is happening ! This is just so Bizzare.
Try storing the MEASURE value in a variable first
Revenue =
VAR myMEASURE = [MonthYear]
RETURN
CALCULATE (
SUM ( fact[Revenue] ),
FILTER ( fact, fact[fiscalMonth] = myMEASURE )
)
That worked like a Charm.
Thank You.
Honestly, I was trying to do this FORMAT(Measure, "String") , that was not working.
This worked.
Glad it worked.
When you use a MEASURE inside a FILTER function, it is evaluated for each row of the Table inside FILTER function (in your case it is FACT table)
That's why we have to store its value first in a variable
FILTER ( fact, fact[fiscalMonth] = [Measure] ) )