cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lcbdax Frequent Visitor
Frequent Visitor

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!
3 REPLIES 3
Community Support Team
Community Support Team

Re: SWITCH terrible performance issue

Hi @lcbdax ,

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.

 

lcbdax Frequent Visitor
Frequent Visitor

Re: SWITCH terrible performance issue

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

lcbdax Frequent Visitor
Frequent Visitor

Re: SWITCH terrible performance issue

Unfortunately there are no improvements

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 398 members 3,275 guests
Please welcome our newest community members: