DAX – IF Performance Tuning Using Variables

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] = 01, [Median Base] )
    )
[Median Base] – Is a complex formula which is evaluated twice. So we can rewrite the query in the following way:

Ult Loss Ratio :=
[UltLRBase] * PRODUCTX (
        ALL ( ‘Date'[UnderwritingYear] ),
VAR MedianVar = [Median Base] RETURN IF ( MedianVar = 01, 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] = 01, [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 = 01, 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

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.
You need to agree with the terms to proceed