Introduction
In my last two posts If Only I Could Avoid IF and Another DAX IF/SWITCH Performance Story I proposed some techniques which could be used to tune IF statements in your DAX calculations. Meanwhile, I migrated the first projects to SSAS 2016 and I had some time to look into painful formulas again.
DAX Variables Role in IF Performance Tuning
There is one new useful feature of the DAX language in 2016 which needs to be considered when you think about performance tuning – Variables. As Alberto mention in this article – “Variables are a major feature that makes writing DAX code easier. Moreover, it greatly increases the readability and reusability of your code.” Initially, it was not obvious to me that it could be used for IF statement performance tuning. But there is another important fact about Variables which is mentioned at the end of the article – “Variables lead to the single evaluation of a complex subexpression. The DAX optimizer uses variables to guarantee that their evaluation happens only once, resulting in much faster code, whenever you had the same subexpression that needs to be evaluated more than once.” And this is it. So how we can use variables in IF tuning?
Let’s Imagine we have the following formula:
Ult Loss Ratio := [UltLRBase] * PRODUCTX ( ALL ( ‘Date'[UnderwritingYear] ), IF ( [Median Base] = 0, 1, [Median Base] ) ) |
Ult Loss Ratio := [UltLRBase] * PRODUCTX ( ALL ( ‘Date'[UnderwritingYear] ), VAR MedianVar = [Median Base] RETURN IF ( MedianVar = 0, 1, MedianVar ) ) |
This small change leads to evaluating the formula only once and therefore it decreases evaluation time by half.
The nice part about variables is you can use them on almost any place of expression and it takes the current evaluation context of that expression part. The first formula I used is simplified for better understanding.
This is a real life usage – formula with performance problem which can be tuned with just a small change (marked as yellow):
Ult Lost Ratio Median := [Ult Lost Ratio Base] * PRODUCTX ( FILTER ( ALL ( ‘UY'[UY] ), ‘UY'[UY] >= [ReportingYear] – MAX ( UY[Treaty year v] ) + 2 && ‘UY'[UY] <= 12 ), CALCULATE ( IF ( [Median DF] = 0, 1, [Median DF] ), FILTER ( ALL ( UY[Treaty year v] ), UY[Treaty year v] >= [ReportingYear] – 11 && UY[Treaty year v] <= [ReportingYear] ) ) ) |
And the following is fixed with improved performance:
Ult Lost Ratio Median:= [Ult Lost Ratio Base] * PRODUCTX ( FILTER ( ALL ( ‘UY'[UY] ), ‘UY'[UY] >= [ReportingYear] – MAX ( UY[Treaty year v] ) + 2 && ‘UY'[UY] <= 12 ), VAR MedianCalculated = CALCULATE ( [Median DF], FILTER ( ALL ( UY[Treaty year v] ), UY[Treaty year v] >= [ReportingYear] – 11 && UY[Treaty year v] <= [ReportingYear] ) ) RETURN IF ( MedianCalculated = 0, 1, MedianCalculated ) ) |
Summary
If you use SSAS 2016 you can basically skip my first two blogs as all workarounds proposed there can be done in a different way – more elegantly and transparently with Variables.
2 Comments. Leave new
Thank alot. Great information!! Solved my performance issue.
Thanks, I’m happy it helped.