How to enhance the refresh performance regarding Switch funtion

[sean_hong]

Hi all, 

I am quite new for PowerBI.  I would like to ask for help. 

I made the PowerBI dashboard which reads the report slowly. 

I assume that the below DAX affects the whole dashboard performance. 

Is there any way to enhance this using VAR function?

DAX_name =

SWITCH(

TRUE()

, SELECTEDVALUE(wbr[measure_type]) IN {""name1", "name2", "name3"}, IFERROR( SUM(table[measure1]), 0)

, SELECTEDVALUE(wbr[measure_type]) IN {"name4", "name5", "name6"}, divide(sum(table[measure1]), sum(table[measure2]), 0)

, SELECTEDVALUE(wbr[measure_type]) IN {"name7"}, IFERROR( CALCULATE(Table2[measure3]*100), 0)

, SELECTEDVALUE(wbr[measure_type]) IN {"name8", "name9"}, (divide( sum(table[measure1]), sum(table[measure2]), 0)*1000000)

, SELECTEDVALUE(wbr[measure_type]) IN {"name10"}, IFERROR( CALCULATE( SUM(table[measure1]), LASTDATE('calendar'[Date]) ), 0)

, SELECTEDVALUE(wbr[measure_type]) IN {"name11"}, IFERROR( CALCULATE( ( (sum(table[measure1])/sum(table[measure2]))*100 ), LASTDATE('calendar'[Date]) ), 0)

, SELECTEDVALUE(wbr[measure_type]) IN {"name12", "name13", "name14"}, divide( sum(table[measure1]), sum(table[measure2]), 0)*100

)

[sean_hong]

Fantastic!. It perfectly works. Thak you so much !!

[v-tangjie-msft · Accepted solution]

Hi @sean_hong ,

You can try the following measure.

DAX_name =
var _a=SELECTEDVALUE(wbr[measure_type]) IN {"name1", "name2", "name3"}
var _b=IFERROR( SUM(table[measure1]), 0)
var _c=SELECTEDVALUE(wbr[measure_type]) IN {"name4", "name5", "name6"}
var _d=divide(sum(table[measure1]), sum(table[measure2]), 0)
var _e=SELECTEDVALUE(wbr[measure_type]) IN {"name7"}
var _f=IFERROR( CALCULATE(Table2[measure3]*100), 0)
var _g=SELECTEDVALUE(wbr[measure_type]) IN {"name8", "name9"}
var _h=divide( sum(table[measure1]),sum(table[measure2]), 0)*1000000
.....

var _result=
SWITCH(
TRUE()
, _a, _b
, _c, _d
, _e, _f
,_g , _h)

return _result

Please refer to the following document for more information.

Use variables to improve your DAX formulas - DAX | Microsoft Learn

VAR – DAX Guide

Using Variables within DAX - (powerbi.tips)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution to help the other members find it more quickly.