Improve performance of Power BI application using variables in DAX

[Sinson]

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 =

var PercentCum=HealthCheck[Percentage **bleep**]

return

if(PercentCum>=0.85,75,if(PercentCum>=0.7 && PercentCum< 0.85,62.5,if(PercentCum< 0.70 && PercentCum>=0.35, 37.5,if(PercentCum<0.35 && PercentCum>=0.25,25,if(PercentCum<0.25 && PercentCum>=0.15,12.5,if(PercentCum<=0.15 && PercentCum>=0,0, 0))))))*HealthCheck[Criticality]

This variable implementation saves the memory and perform a faster results. For using the variables we need to use the return statement for the condition.

[harshnathani · Accepted 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!

[harshnathani · Accepted 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!

[Anonymous · Accepted 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