Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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] )
)
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.
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!
As I said those were just examples to show the idea; statements and their sequence depend on your dataset and objective.
@johnsmith92 I would get rid of IFERROR; instead check conditions that could cause an error before main calculation.
You mean I should use couple of nested IF statements?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |