SWITCH terrible performance issue

[Anonymous]

Hello everyone,

I am making a dynamic formula that depending on user selection should choose the underlying measure to kick-in.
I have used this technique a couple of times and never had issue, but now its incredible slow.

My code is the following:

test = 
var hasfilter = HASONEFILTER('Sales Reset'[ResetValue])
var salesMeanrolling = [SalesRolling Mean]
var valueselected = VALUES('Sales Reset'[ResetValue])

return

IF(hasfilter,
SWITCH(TRUE(),valueselected =0,[SalesNoReset Mean],
valueselected =1,salesMeanrolling,
valueselected =2,[SalesReset2M Mean],
valueselected =3,[SalesReset3M Mean],
valueselected =4,[SalesReset4M Mean],
valueselected =6,[SalesReset6M Mean],
valueselected =12,[SalesReset12M Mean]),salesMeanrolling)

The performance of using the direct formula vs using the direct formula is insanely different and I cant comprehend why..
I am assuming the valueselected condition gets evaluated fast thus jumping quite fast to the underlying measure, but somehow this is not happening.

Any ideas on why such behavior can happen?

Thanks!

[v-xuding-msft]

Hi @Anonymous ,

I think it is caused by the large data and complex calculation. I modified your formula. It might calculate a little faster.

test = 
var valueselected = SELECTEDVALUE('Sales Reset'[ResetValue])
return

SWITCH(
HASONEFILTER('Sales Reset'[ResetValue]),
valueselected =0,[SalesNoReset Mean],
valueselected =1,salesMeanrolling,
valueselected =2,[SalesReset2M Mean],
valueselected =3,[SalesReset3M Mean],
valueselected =4,[SalesReset4M Mean],
valueselected =6,[SalesReset6M Mean],
valueselected =12,[SalesReset12M Mean], [SalesRolling Mean])

Best Regards,

Xue Ding

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

[Anonymous]

Thanks for the reply. In my case I cannot use the SELECTEDVALUE as its an older Tabular version but will use the equivalent and give it a go with your approach and reply here then

[Anonymous]

Unfortunately there are no improvements

[Anonymous]

Hi @Anonymous 

I noticed same behaviour in my dataset, and i found some insipiration on this link: https://www.sqlbi.com/articles/optimizing-mutually-exclusive-calculations/

IS it possible to rewrite your measures so that you user switch to calcualte filter and then call only one or lower number calcualte statements

test = 
var valueselected = SELECTEDVALUE('Sales Reset'[ResetValue])
var vFilter=

SWITCH(
HASONEFILTER('Sales Reset'[ResetValue]),
valueselected =0,"SalesNoReset Mean",
valueselected =1,"salesMeanrolling",
valueselected =2,"SalesReset2M Mean",
valueselected =3,"SalesReset3M Mean",
valueselected =4,"SalesReset4M Mean",
valueselected =6,"SalesReset6M Mean",
valueselected =12,"SalesReset12M Mean"
, "SalesRolling Mean")

RETURN CALCULATE([SalsesMeasure],FilterColumn = vFilter)