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.
Anonymous wrote:
Hi everyone,
I'm writting here to get some help to optimize the calculation of one of my measure that takes too much time to compute (10 seconds everytime you click on something / expand a row)
Let me explain the report :
Model :
- I've managed to get a Star Schema with a Fact Table based with two base measure : Turnover and CountArticle
- The date granularity is Quarter - Year : it is already aggregated.
- I have many dimensions but in this example, I only use 4 :
- Date
- Seller Store : Sells an article
- Article : Description of an article
- Customer : Buys an article. It has a dedicated SalesMan and SalesTeam
Model Size :
About 2 000 000 records in Fact Table.
Dimension are way smaller. (Except Customer : I've loaded the entire dimension : Maybe not optimized)
Subject :
- Sales on article that are sold by my company.
Slicers :
- Year
- Quarter
- IsLogistic (feature of an article : used to exclude / include logistic products)
Visual
- Matrix Visual
- Hierarchy
- Sales Team
- Sales Man
- Customer
- Seller Store
- Article
- Values
var __year = selectedvalue( 'Date'[Year] )
var __quarter = selectedvalue( 'Date'[Quarter] )
var __result =
CALCULATE (
[SumTurnover],
'Date'[Year] = __year - 1,
'Date'[Quarter] = __quarter,
ALL( 'Date' )
)
return __result
var __year = selectedvalue( 'Date'[Year] )
var __quarter = selectedvalue( 'Date'[Quarter] )
var __result =
CALCULATE (
[SumCountArticle],
'Date'[Year] = __year - 1,
'Date'[Quarter] = __quarter,
ALL( 'Date' )
)
return __result
sumx(
Article,
var __sumTurnover = [SumTurnover]
var __sumCountTest = [SumCountTest]
var __sumTurnoverLY = [SumTurnoverLastYear]
var __sumCountTestLY = [SumCountTestLastYear]
var __result = (
DIVIDE ( __sumTurnover, __sumCountTest )
- DIVIDE ( __sumTurnoverLY, __sumCountTestLY )
) * __sumCountTestLY
var __shouldCalc =
_sumTurnover <> 0
&& _sumTurnoverLY <> 0
&& _sumCountTest] <> 0
&& _sumCountTestLY] <> 0
return
IF( __shouldCalc, __result )
)
Try it and see what you get...
Best
D
You can also try to optimize the iteration in SUMX by doing this...
var __articles = calculatetable( Article, Fact ) return sumx( __articles, ... // rest as in my prev post )Best
D
Hi Everyone,
I'll try all of your tips.
Unfortunately I think that SummarizeColumn cannot be used in Measure (as it is written in The Definitive Guide To DAX)
I will investigate the others parts of the Measure.
Thanks