Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

SWITCH terrible performance issue

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!
Need Help

4 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        Unfortunately there are no improvements