The difference between two measures

[Casteless]

Hello, 

I Wrote the following measure first and it computed correctly but it took ~15 minutes and msmdsrv.exe spiked bringing the whole computer to a halt.

The date filters in both measures are just grabbing the current filter context "Reserve[Month]" and calculating a date range for the month 3 months prior. For example if the current filter context is 10/1/2016, the first measure would include rows with an [EnterDate] that had Month 7 and year 2016, grabbing 7/1/2016-7/31/206. The second measure overtly calculates 7/1/2016 and 7/31/2016 based on the the filter context and includes rows that fall between them. 

I'm having trouble understanding why the second method is so much more efficient.

Relevant lines are underlined.

Measure1 = CALCULATE(SUM(Master[Open Balance]),
Master[FPD Class]="30 Non-Recovery"||Master[FPD Class]="60 Non-Recovery",
Master[DOFP]<Today(),
FILTER(Master,MONTH(Master[EnterDate])=MONTH(DATEADD(Reserve[Month],-3,MONTH))),
FILTER(Master,YEAR(Master[EnterDate])=YEAR(DATEADD(Reserve[Month],-3,MONTH))))
/
CALCULATE(SUM(Master[Open Balance]),
FILTER(Master,MONTH(Master[EnterDate])=MONTH(DATEADD(Reserve[Month],-3,MONTH))),
FILTER(Master,YEAR(Master[EnterDate])=YEAR(DATEADD(Reserve[Month],-3,MONTH))),
Master[DOFP]<TODAY())

This second measure calculated in seconds with no movement in msmdsrv.exe.

Measure2 =
var reportdate=CALCULATE(DISTINCT(VALUES(Reserve[Month])))
var startdate=CALCULATE(DATEADD(DISTINCT(VALUES(Reserve[Month])),-3,MONTH))
var enddate=CALCULATE(DATEADD(DISTINCT(VALUES(Reserve[Month])),-2,MONTH))-1

RETURN CALCULATE(SUM(Master[Open Balance]),
Master[FPD Class]="30 Non-Recovery"||Master[FPD Class]="60 Non-Recovery",
Master[DOFP]<Today(),
Master[EnterDate]<=enddate&&Master[EnterDate]>=startdate)/
CALCULATE(SUM(Master[Open Balance]),
Master[EnterDate]<=enddate&&Master[EnterDate]>=startdate,
Master[DOFP]<TODAY())

[v-huizhn-msft · Accepted solution]

Hi @Casteless,

In your first measure, repeat the calculation for startdate and enddate, you had the same subexpression that needs to be evaluated more than once. While in the second measure, variables lead to the single evaluation of complex subexpression. The DAX optimizer use variables to guarantee that their evaluation happens only once, resulting in much faster code.

Using variable in DAX, you can split the calculation in several steps in a more readable way, without paying the cost of storing intermediate results in calculated columns. Variables are a major feature that makes writing DAX code easier. For more details, please review this article.

Best Regards,
Angelia