SSAS Tabular – Another DAX IF/SWITCH Performance Story

As I wrote In last blog I have experienced IF/Switch statement having a huge performance drop especially in situations where the calculation behind is complex. I’ll explain latest IF story in this blog.

Let’s imagine we want to implement a measure whose behavior is driven by parameter dimension. So, we have table Report View which drives the behavior of our calculation. We expect to always retrieve four columns into our reports and then the behavior of those columns would be driven by other Report View table columns. This might be more understandable based on an example:

  • Report View 1 – “Current Year vs Previous Year”
    • Column 1 – YTD Previous Year
    • Column 2 – YTD Current Year
    • Column 3 – Current Year Delta = Column 1 – Column 2
    • Column 4 – Current Year % Performance = Column 3/Column1
  • Report View 2 – “Current Year vs Plan”
    • Column 1 – YTD Current Year
    • Column 2 – YTD Plan
    • Column 3 – Plan Delta = Column 1 – Column 2
    • Column 4 – Plan % Performance = Column 3/Column1

We would have more such Report Views so it makes perfect sense to implement dynamic behavior into our measure which would be driven by parameters such as Report View, Financial Year and Financial Quarter. The behavior I wanted to reach is better understandable from this Excel based screenshot. You can see the parameters in a slice of this Pivot Table:

You can see the behavior of intermediate calculated column values on screenshot above as well. The column number would be part of report and it would drive part of the dynamic behavior as well. Everything works fine unless you try to merge the behavior of Column1 – Column 4 into one measure as you can see in the figure above. The statement would look like this:

ReportAmount2 :=
IF (
    HASONEVALUE ( ‘Reporting View'[Report Column] ),
SWITCH (
        VALUES ( ‘Reporting View'[Report Column] ),
“Column 1”, [ReportAmountC1],
“Column 2”, [ReportAmountC2],
“Column 3”, [ReportAmountC2AllColumns] – [ReportAmountC1AllColumns],
“Column 4”DIVIDE ( [ReportAmountC3AllColumns], [ReportAmountC1AllColumns], BLANK () ),
BLANK ()
    ),
BLANK ()
)

Although ReportAmountN measures works fine if used alone, when you put them into this switch the entire performance decreases significantly. So, we need to think about an approach for removing it from this place and moving it elsewhere. DAX is much stronger in math based functions than in condition based therefore I would use the following approach:

In measure Amount we just sum Column 1 – Column 4 measures. Therefore core functionality for ReportAmoundCN measure, is to return value when it has a context of ReportView[Column N], otherwise return empty. You could use CALCULATE for this but it has some difficulties in this scenario. Because of this I decided to use IF + Math. I would move IF to the place where evaluation is fast. Which means directly to the Reporting View table. Each column would have own measure CNIndicator. An example of if statement for Column 3:

C3Indicator :=
IF (
    HASONEVALUE ( ‘Reporting View'[Report Column] ),
IF ( VALUES ( ‘Reporting View'[Report Column] ) = “Column 3”10 ),
0
)

Calculation of Column 3 measure for fact is then enhanced by division by this factor. In case we divide by 0 we give Empty Value as result.

ReportAmountC3 :=
DIVIDE (
    [ReportAmountC2AllColumns] – [ReportAmountC1AllColumns],
[C3Indicator],
BLANK ()
)

Then we can simply calculate measure Amount like this:

ReportAmount := [ReportAmountC1] + [ReportAmountC2] + [ReportAmountC3] + [ReportAmountC4]

The performance of this approach is much faster than IF/SWITCH statement implemented directly with complicated measures in it. If you have just a few dimensions/rows in your analyses both measures seem to perform same. But when I added a 4th dimension into my pivot table, which resulted in 700 rows and 4 columns, performance of measure with SWITCH statement significantly dropped even in 2016 version of SSAS.

ReportAmount measure – 160 ms

ReportAmount2 measure – I killed query after 3 minutes…

3 Comments. Leave new

  • Harry Arends
    16. 1. 2018 10:49

    Hi Roman,
    Great post. Do you have an example PBIX file for this issue?
    Thanks!

    Reply
    • Roman Lánský
      31. 1. 2018 20:09

      Hi Harry,
      unfortunately I do not have any. Whole tested and tuned in SSAS Tabular.

      Reply
  • Thank you so much for this post. Had exactly the same issue and had dramatic performance improvement

    Reply

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