Forum Discussion
Optimize SUMX / SUMMARIZE Measure
Hello, I'm not sure if you have figured out why it was slow, but I think it's probably
your if statement in your SUMX.SummedPriceImpact = SUMX ( SUMMARIZE ( 'Fact', 'Customer'[Sales Team], 'Customer'[Sales Man], 'Customer'[Customer], 'Store'[Store], 'Article'[Article] ), [PriceImpact] )I see 2 things :
1) [PriceImpact] triggers Context transition for each iteration which is bad with a Big Fact table
2) You should always avoid using IF statement inside an Iterator like SUMX. The use of IF statement will require a call to the Formula Engine for each iteration and the iteration is done by the Vertipaq engine.
One solution to speed up the process would be to precalculated your [PriceImpac] measure inside a Calculated column in your Article Dimension. It will be stored in memory.
Maybe I did not understand your problem very well and this won't work for you.
Also 2M rows is nothing for a Tabular model. You should be able to have a speed < 1sec like the other person said earlier.
Have you tried writing like this?
SUMMARIZECOLUMNS (
'Customer'[Sales Team],
'Customer'[Sales Man],
'Customer'[Customer],
'Store'[Store],
'Article'[Article],
'Fact'
)
Using variables instead of repeating could actually help quite a lot. But it could sometimes be optimized in the DAX engine and then it doesn't help at all. In earlier versions of Analysis Services I have seen huge savings by not repeating measures.
One thing I noticed is that even FILTERs can be stored in variables, like this:
Var LastYearFilter = FILTER (
ALL ( 'Date' ),
'Date'[Year]
= MAX ( 'Date'[Year] ) - 1
&& 'Date'[Quarter] = MAX ( 'Date'[Quarter] )
)
Then you can use that in your both measures for last year (if you move them into a single measure). But I don't know if it has any noticable impact on performance...
IF statements can sometimes be really bad for performance. But I don't know in this case how to optimize it.
I have done similar calculations myself a few times and since it's a complex problem it will always take some time to calculate. But 10 seconds sounds a lot for 2 million rows.
Best
D