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:
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.2, 1 ) )
The results, as shown in DAXStudio on a warm cache, show a total time of 113 milliseconds:
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:
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.