Rewrite SUMX? Poor performance when running calculations

[johnsmith92]

Hi all,

I have the following table:

Queue	AgentName	Year	Quarter	Month	Score1	Weight1
1	John	2021	1	January	100	6.87
1	John	2021	2	June	66,67	5.357
1	John	2021	3	September	33,33	5.221
2	Jane	2020	2	June	100	5.228
2	Jane	2020	3	September	100	6.647

I am trying to dynamically calculate the weighted score based on what is in my visual - so it can go in depth from year, all the way to week.

I have the following query:

WeightedScore1 :=
ROUND (
    IFERROR (
        SUMX ( FactScore; FactScore[Score1] * FactScore[Weight1] )
            / SUM ( FactScore[Weight1] );
        IF (
            CALCULATE ( SUM ( FactScore[Score1] ) ) > 0;
            CALCULATE ( SUM ( FactScore[Score1] ) ) / CALCULATE ( COUNT ( FactScore[Score1] ) );
            0
        )
    );
    1
)

This works perfectly with the little caveat that it takes a lot to load since I have a table with 3 millions rows (which will keep growing) and 4 more scores to evaluate. I believe the culprit is the SUMX function so how can I replace it in this query and improve performance?

NB: I think this might be important - I am running this from SSAS Tabular Cube (on SQL server 2014) through a live connection. When I run the exact same measures from an excel import it works very quickly. 

Thanks in advance!

[Anonymous]

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] )
)

[johnsmith92]

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.

[v-kelly-msft]

Hi @johnsmith92 ,

You could use 

CALCULATE(FactScore[Score1] * FactScore[Weight1];FactScore)

instead of 

SUMX ( FactScore; FactScore[Score1] * FactScore[Weight1] )

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

As I said those were just examples to show the idea; statements and their sequence depend on your dataset and objective.

[Anonymous]

@johnsmith92 I would get rid of IFERROR; instead check conditions that could cause an error before main calculation. 

[johnsmith92]

You mean I should use couple of nested IF statements?