Forum Discussion

johnsmith92's avatar
johnsmith92
Regular Visitor
4 years ago

Rewrite SUMX? Poor performance when running calculations

Hi all,

 

I have the following table:

 

QueueAgentNameYearQuarterMonthScore1Weight1
1John20211January1006.87

1

John20212June66,675.357
1John20213September33,335.221
2Jane20202June1005.228
2Jane20203September1006.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!

 

 

 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

    • johnsmith92's avatar
      johnsmith92
      Regular Visitor
      4 years ago

      You mean I should use couple of nested IF statements?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      johnsmith92
      Regular Visitor
      4 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

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