Forum Discussion

Casteless's avatar
Casteless
Helper I
9 years ago
Solved

The difference between two measures

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 a...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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