Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
@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.
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.
@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.
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |