Power Pivot: SUMX Performance – “IF Statements”

There are many great articles on how to improve SUMX performance – a quick Google turned up alternatives to nesting SUMX and redundant measures inside SUMX.

One important issue that I feel is often ignored is the use of “complex calculations” inside of a SUMX.  Although I haven’t found an official definition, I can, at the very least, tell you that it means an IF statement.  These calculations are often the culprit behind slow models designed by inexperienced DAX users.

To demonstrate this, I created the below 10 million row table using PowerBI:

2016-01-13 10_36_36-Untitled - Power BI Desktop

If I were to calculate a fee dollar amount – adjusted for the an exchange rate – it may look something like this:

SUMX Slow =
SUMX (
    'tbl_Holdings',
    [Market Value] * [Fee %]
        IF ( [Country] = "USA"1.21 )
)

The results, as shown in DAXStudio on a warm cache, show a total time of 113 milliseconds:

2016-01-13 10_42_53-DaxStudio - 2.3.4

Given that there’s only two countries in this simple scenario, the calculation can be tweaked by replacing the IF statement with Boolean logic:

SUMX Fast :=
 SUMX (
     'tbl_Holdings',
     [Market Value] * [Fee %]
         * ( ( ( [Country] = "USA" )
         1.2 )
         + ( ( [Country] = "CAD" )
         1 ) )
 )

For all intents and purposes the calculation is the same.  And while it’s nowhere near as pretty, the results speak for themselves:

2016-01-13 10_45_32-DaxStudio - 2.3.4

Final Thoughts

Simply put: avoid using an IF statement inside a SUMX.  If needed, there are alternatives such as moving the logic to a calculated column or applying Boolean logic similar to the above example.  If implementing the latter, I urge caution to ensure that the Boolean logic is correctly applied because it is not as intuitive as a simple IF statement.

 

Simon Nuss

Power Pivot: SUMX Performance – “IF Statements”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s