Better Performance with Variables in DAX

The  new features of DAX for SSAS 2016 Tabular, Power BI Desktop, and Excel 2016 Power Pivot is variables. There are three benefits of DAX Variables than we show below:

• Contex Transactiont: The variables are evaluated where you define after VAR  rather than using recursive functions ( ex. EARLIER) coming out of the row context but we can simply refer to a call variable that is already outside the row context
• Performance: When I have many conditions to apply to a single value , as in the example below you can split a query considering a single variable and re-using for each condition.
• Readability: Moving the logic conditions and filtering DAX expressions in one variable , the resulting clause of the return code is much more concise.

Now with an examples show the better performance with variables. We show below the measures without variables

now use EVALUATE for test the measure in a DAX Code

and run it with DAX Studio. The result show below

As you can see there are 5 storage engine (SE) requests. If you click on each line, you’ll see that 1 is to get a distinct list of ProductKeys and the other 3 are retrieving the sum of SalesAmount  by ProductKey

We show below the measures with variables

now use EVALUATE for test the measure in a DAX Code

and run it with DAX Studio. The result show below

there are only 2 SE requests, one for the distinct list of ProductKeys and second for the sum of SalesAmount by ProductKey.

Conclusion
Also the query used is too simple to show the performance improvement but has realized the most simple queries plans and fewer requests SE.