SUMX faster than SUM?

[BIanon]

Hello Community,

I was trying to performance tune my model a bit and came across a situation that I do not understand and cannot explain.

I have a single Fact table, let's call it Table1. I am currently interating over the table using SUMX() while multiplying two values, in my mind, if I multiplied them in Power Query and just used a SUM() on the output column, that should be faster - but apparantly it is twice as slow which I cannot wrap my head around.

Measure one using SUMX:

SUMX('Table1', 'Table1'[Value] * 'Table1'[Multiplier])

Dax Studios timing:
Cold, average 22,4
Warm, average 1,4

Measure using SUM:

SUM( 'Table1'[MultipliedValue] )

Cold, average 51,6
Warm, average 3 

Can someone please explain to me how iterating over a table and multiplying to value + summing can be faster than just summing?

[SridharP]

When its comes to using the Iterators in measure , Iterators are faster than standard aggregators. Aggregators just syntex sugar coated  version of iterators.
The difference of syntex might make you look it like a slower version.

[SridharP]

You could also refer it in the Definitive guide to DAX Book by Mark Russo and  Albert Ferrari

[SridharP]

We can find a different opinion in using the iterators in column calculations.

[Greg_Deckler]

@BIanon I couldn't quite replicate this performance difference. Are you just using Card visuals or is there something else in your visuals that you are grouping on? It's a common misperception that X aggregators are slow but I have never found that to be the actual case when performance testing. X aggregators are actually very performant.

[BIanon]

Thank you for your response.

I just tried with test data, only like 1k rows, and am not able to replicate the issue with test data either 😞

I connect to the PBIX via DAX Studios and just do an

EVALUATE
{ [Measure] }

I must have ****ed up something in my model since I am seeing this happen.

[Greg_Deckler]

@BIanon That's exactly how I tried to replicate it using data generated from mockaroo.com and using Card visuals. Typically I would expect that SUM and SUMX would return essentially similar performance results, within a few dozen milliseconds of one another and it might vary as to which is faster between runs. This is exactly the results I received in my testing. I was using the Performance Analyzer pane.