Forum Discussion
Rewrite SUMX? Poor performance when running calculations
I mean use IF statements which check conditions that could cause an error before main calculation. For example:
WeightedScore1 :=
ROUND (
IF (
CALCULATE ( SUM ( FactScore[Score1] ) ) > 0;
CALCULATE ( SUM ( FactScore[Score1] ) ) / CALCULATE ( COUNT ( FactScore[Score1] ) );
SUMX ( FactScore; FactScore[Score1] * FactScore[Weight1] )
/ SUM ( FactScore[Weight1] )
);
1
)Another point is if you have many nested IF statements then it would be better use SWITCH function (following code is just an example):
SWITCH ( TRUE();
CALCULATE ( SUM ( FactScore[Score1] ) ) > 0;
CALCULATE ( SUM ( FactScore[Score1] ) ) / CALCULATE ( COUNT ( FactScore[Score1] ) );
CALCULATE ( SUM ( FactScore[Score1] ) ) = 0;
0;
CALCULATE ( SUM ( FactScore[Score1] ) ) < 0;
SUMX ( FactScore; FactScore[Score1] * FactScore[Weight1] ) / SUM ( FactScore[Weight1] )
)
- johnsmith924 years agoRegular Visitor
Thanks for the follow up, but if I'm not mistaken, what you suggest would end up evaluating the
CALCULATE ( SUM ( FactScore[Score1] ) ) / CALCULATE ( COUNT ( FactScore[Score1] ) ) statement first
and this is my backup statement in case I have 0s in the weight column. But your suggestion of removing IFERROR has done wonders to my query. Now I just need to figure out the conditional calculation.
- Anonymous4 years agoNot applicable
As I said those were just examples to show the idea; statements and their sequence depend on your dataset and objective.
- v-kelly-msft4 years ago
Community Support
Hi johnsmith92 ,
You could use
CALCULATE(FactScore[Score1] * FactScore[Weight1];FactScore)instead of
SUMX ( FactScore; FactScore[Score1] * FactScore[Weight1] )Best Regards,
KellyDid I answer your question? Mark my reply as a solution!