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.
Measure calculation is also based on how many columns along with the measure your are displaying in visual.
If you use single column and measure it will give you result in 1 2 sec.
Thanks
Pravin
Anonymous,
10 sec for 2 million rows? Mate, are you joking?.... I've got 100s of millions in my models and I get speed under 1 sec for similar simple measures. Of course, this is dependent on how powerful box you have but for 2 million rows in a fact table I'd really expect the speed of simple measures to be under 1 sec.
Best
D