Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
We can improve the performance of a calculated column by adding variables in the conditional statements.
Consider the below example which return the value based on the conditional statment.
Score Cumulative =
if(HealthCheck[Percentage **bleep**]>=0.85,75,if(HealthCheck[Percentage **bleep**]>=0.7 && HealthCheck[Percentage **bleep**]< 0.85,62.5,if(HealthCheck[Percentage **bleep**]< 0.70 && HealthCheck[Percentage **bleep**]>=0.35, 37.5,if(HealthCheck[Percentage **bleep**]<0.35 && HealthCheck[Percentage **bleep**]>=0.25,25,if(HealthCheck[Percentage **bleep**]<0.25 && HealthCheck[Percentage **bleep**]>=0.15,12.5,if(HealthCheck[Percentage **bleep**]<=0.15 && HealthCheck[Percentage **bleep**]>=0,0, 0))))))*HealthCheck[Criticality]
The value which we are planning to compare, we need to assign it into a variable. So that the memory usage can be optimised for a larger data set in power BI desktop. For the above statement my Power BI visuals given error/exception due to the memory overflow. Later I fixed it with the below code that uses a variable to save the value and compare that variable given to the condition.
Score Cumulative =
Solved! Go to Solution.
Hi @Sinson ,
Thanks for sharing.
With the SWITCH() statement.
Score Cumulative =
VAR PercentCum = HealthCheck[Percentage **bleep**]
RETURN
SWITCH (
TRUE (),
PercentCum >= 0.85, 75,
PercentCum >= 0.7
&& PercentCum < 0.85, 62.5,
PercentCum < 0.70
&& PercentCum >= 0.35, 37.5,
PercentCum < 0.35
&& PercentCum >= 0.25, 25,
PercentCum < 0.25
&& PercentCum >= 0.15, 12.5,
PercentCum <= 0.15
&& PercentCum >= 0, 0,
0
) * HealthCheck[Criticality]
https://www.sqlbi.com/articles/optimizing-if-and-switch-expressions-using-variables/
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
// No idea why you guys always write more code than necessary...
[Score Cumulative] =
VAR PercentCum = HealthCheck[Percentage **bleep**]
VAR Criticality = HealthCheck[Criticality]
VAR Output =
Criticality
* SWITCH( TRUE(),
PercentCum >= 0.85, 75,
PercentCum >= 0.7, 62.5,
PercentCum >= 0.35, 37.5,
PercentCum >= 0.25, 25,
PercentCum >= 0.15, 12.5,
PercentCum >= 0, 0,
0
)
RETURN
Output
Best
D
Hi @Sinson ,
Thanks for sharing.
With the SWITCH() statement.
Score Cumulative =
VAR PercentCum = HealthCheck[Percentage **bleep**]
RETURN
SWITCH (
TRUE (),
PercentCum >= 0.85, 75,
PercentCum >= 0.7
&& PercentCum < 0.85, 62.5,
PercentCum < 0.70
&& PercentCum >= 0.35, 37.5,
PercentCum < 0.35
&& PercentCum >= 0.25, 25,
PercentCum < 0.25
&& PercentCum >= 0.15, 12.5,
PercentCum <= 0.15
&& PercentCum >= 0, 0,
0
) * HealthCheck[Criticality]
https://www.sqlbi.com/articles/optimizing-if-and-switch-expressions-using-variables/
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
// No idea why you guys always write more code than necessary...
[Score Cumulative] =
VAR PercentCum = HealthCheck[Percentage **bleep**]
VAR Criticality = HealthCheck[Criticality]
VAR Output =
Criticality
* SWITCH( TRUE(),
PercentCum >= 0.85, 75,
PercentCum >= 0.7, 62.5,
PercentCum >= 0.35, 37.5,
PercentCum >= 0.25, 25,
PercentCum >= 0.15, 12.5,
PercentCum >= 0, 0,
0
)
RETURN
Output
Best
D
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
40 | |
19 | |
12 |