DAX Variable

[gavs73]

Hi, I'm having trouble calling a variable in DAX ( assuming it is actually possible )...

I have a number of DAX statements similar to this:

d25:=CALCULATE([Creditx25],Data[Month]="32016",Data[PRODUCT]="Display")

Rather than changing "32016", I'd like to declare it as a variable, LMonth:="32016"

I've tried several ways of calling it, but all I get is errors...
d25:=CALCULATE([Creditx25],Data[Month]=[LMonth],Data[PRODUCT]="Display")
d25:=CALCULATE([Creditx25],Data[Month]=LMonth,Data[PRODUCT]="Display")
d25:=CALCULATE([Creditx25],Data[Month]='LMonth',Data[PRODUCT]="Display")
d10:=CALCULATE([Creditx10],Data[Month]=([LMonth]),Data[PRODUCT]="Display")

Thanks
Gav

 

[Ozeroth]

Hi Gav,

When you use a boolean expression as a filter argument for CALCULATE, it can't reference a measure (see https://msdn.microsoft.com/en-us/library/ee634825.aspx).

To get the result you want, you will have to use a table as your filter argument instead.

Something like this with a FILTER function should do it:

d25 :=
CALCULATE (
    [Creditx25],
    FILTER ( ALL ( Data[Month] ), Data[Month] = [LMonth] ),
    Data[PRODUCT] = "Display"
)

Note: The reason I used FILTER( ALL ( Data[Month] )...) is to replicate the 'overwrite' operation that CALCULATE would have performed if your filter argument were Data[Month] = "32016". In other words, existing filters on Data[Month] are cleared and replaced with the value of [LMonth] in the above measure.

Owen

 

[gavs73]

Thank you