topic Variable as a single measure in DAX Commands and Tips

Variable as a single measure

George1973 — Tue, 30 Mar 2021 18:52:38 GMT

Hi,

I have a complex measure with some variables:

Weighted Average = VAR OneLessMonthDailySales=DIVIDE( CALCULATE([Sold Prod Qnty],DATEADD(DateKey[Date],-1,MONTH)), CALCULATE([Unique Sales Day Count],DATEADD(DateKey[Date],-1,MONTH)),0) VAR TwoLessMonthDailySales=DIVIDE( CALCULATE([Sold Prod Qnty],DATEADD(DateKey[Date],-2,MONTH)), CALCULATE([Unique Sales Day Count],DATEADD(DateKey[Date],-2,MONTH)),0) VAR ThreeLessMonthDailySales=DIVIDE( CALCULATE([Sold Prod Qnty],DATEADD(DateKey[Date],-3,MONTH)), CALCULATE([Unique Sales Day Count],DATEADD(DateKey[Date],-3,MONTH)),0) VAR FourLessMonthDailySales=DIVIDE( CALCULATE([Sold Prod Qnty],DATEADD(DateKey[Date],-4,MONTH)), CALCULATE([Unique Sales Day Count],DATEADD(DateKey[Date],-4,MONTH)),0) VAR FiveLessMonthDailySales=DIVIDE( CALCULATE([Sold Prod Qnty],DATEADD(DateKey[Date],-5,MONTH)), CALCULATE([Unique Sales Day Count],DATEADD(DateKey[Date],-5,MONTH)),0) VAR SixLessMonthDailySales=DIVIDE( CALCULATE([Sold Prod Qnty],DATEADD(DateKey[Date],-6,MONTH)), CALCULATE([Unique Sales Day Count],DATEADD(DateKey[Date],-6,MONTH)),0) VAR WeightedAverage= (3*OneLessMonthDailySales+2.5*TwoLessMonthDailySales+2*ThreeLessMonthDailySales+1.5*FourLessMonthDailySales+1*FiveLessMonthDailySales)/10 Return WeightedAverage*[Unique Sales Day Count]

As you can see some outer measures have been repetedly presented in that expession such as [Sold Prod Qnty] and [Inique Sales Day Count], I would like to replace those repeted measure by single variable difining them from the very begining, like in the following examle:

Weighted Average = VAR SQ=[Sold Prod Qnty] VAR SD=[Unique Sales Day Count] VAR OneLessMonthDailySales=DIVIDE( CALCULATE(SQ,DATEADD(DateKey[Date],-1,MONTH)), CALCULATE(SD,DATEADD(DateKey[Date],-1,MONTH)),0) VAR TwoLessMonthDailySales=DIVIDE( CALCULATE(SQ,DATEADD(DateKey[Date],-2,MONTH)), CALCULATE(SD,DATEADD(DateKey[Date],-2,MONTH)),0) VAR ThreeLessMonthDailySales=DIVIDE( CALCULATE(SQ,DATEADD(DateKey[Date],-3,MONTH)), CALCULATE(SD,DATEADD(DateKey[Date],-3,MONTH)),0) VAR FourLessMonthDailySales=DIVIDE( CALCULATE(SQ,DATEADD(DateKey[Date],-4,MONTH)), CALCULATE(SD,DATEADD(DateKey[Date],-4,MONTH)),0) VAR FiveLessMonthDailySales=DIVIDE( CALCULATE(SQ,DATEADD(DateKey[Date],-5,MONTH)), CALCULATE(SD,DATEADD(DateKey[Date],-5,MONTH)),0) VAR SixLessMonthDailySales=DIVIDE( CALCULATE(SQ,DATEADD(DateKey[Date],-6,MONTH)), CALCULATE(SD,DATEADD(DateKey[Date],-6,MONTH)),0) VAR WeightedAverage= (3*OneLessMonthDailySales+2.5*TwoLessMonthDailySales+2*ThreeLessMonthDailySales+1.5*FourLessMonthDailySales+1*FiveLessMonthDailySales)/10 Return WeightedAverage*SD

As you can see the measures [Sold Prod Qnty] and [Inique Sales Day Count] are defined as starting variables "SQ" and "SD" andare used as the replacements of themseves in below given expressions.

..But for some reasons the approach does not work! I do not undestant why.

Please help in clearing the issue.

Thanks

Re: Variable as a single measure

AlexisOlson — Tue, 30 Mar 2021 20:09:52 GMT

Once you define a variable, it's treated as a constant in the rest of the measure. The CALCULATE function changes the evaluation context but a constant is fixed regardless of evaluation context so CALCULATE doesn't do anything to a variable you've already defined. So, for example, OneLessMonthDailySales will be the same as FiveLessMonthDailySales, since shifting the dates doesn't do anything to a value that's already been determined.

Re: Variable as a single measure

George1973 — Fri, 02 Apr 2021 16:58:43 GMT

Thanks for the respond. Now it's crlear and I will be more carefoul with that.
..but it does not solve my problem to optimize the measure perfomance. What could be done instead?

Re: Variable as a single measure

Anonymous — Fri, 02 Apr 2021 18:03:25 GMT

Hi George,

Can you try using

IF.EAGER as in IF.EAGER your value will be computed for only one case when the tested condition is positive. In this way you can set a variable with measure value. This will improve the performace and will solve the issue as well.

Re: Variable as a single measure

George1973 — Fri, 02 Apr 2021 18:13:54 GMT

H,
Can you provide a simple example of the "If.eager", because I have not heard about at so far 🙂

Re: Variable as a single measure

Anonymous — Fri, 02 Apr 2021 18:34:02 GMT

if.eager({condition that you can identify as a flag}, {Write your divide measure here}).

Re: Variable as a single measure

AlexisOlson — Fri, 02 Apr 2021 19:42:34 GMT

I'm not sure if it's necessarily possible to optimize significantly. Because the context is different each time, you need to evaluate each of the prior months separately. I don't see a way to get around that since you can't actually reuse a computation.

That said, you might be able to get some degree of parallelization if you write it differently. See if this works any better:

Weighted Average = VAR PriorMonths = ADDCOLUMNS ( GENERATESERIES ( 1, 5 ), "MonthDailySales", DIVIDE ( CALCULATE ( [Sold Prod Qnty], DATEADD ( 'Date'[Date], -[Value], MONTH ) ), CALCULATE ( [Unique Sales Day Count], DATEADD ( 'Date'[Date], -[Value], MONTH ) ) ), "Weight", ( 7 - [Value] ) / 2 ) VAR WtdAvg = DIVIDE ( SUMX ( PriorMonths, [Weight] * [MonthDailySales] ), SUMX ( PriorMonths, [Weight] ) ) RETURN DIVIDE ( WtdAvg, [Unique Sales Day Count] )

Re: Variable as a single measure

George1973 — Sat, 03 Apr 2021 14:23:18 GMT

WOW, very interesting. I will try it and let you know. Thanks a lot