Any way to stop PowerBI from evaluating every measure in an expression?

[AndrewZ]

Hi

I'm new to PowerBI and have taken over a large model for a large organisation and I'm in the process of trying to speed up the user experience. What I've found is that each slicer applied has a profound effect on speed. On inverstigation I see that the main culprit is the switch between MTD and YTD. If I write a measure for either MTD or YTD and put it in a table I get 1.8 seconds average calc time. If I use a funtion to switch between measures based on a slicer that goes to 4.5 seconds (the full table with all columns this is as long as 90s). The switch statement itself is not slow if I use single values like 1 and 2 but it seems the slowdown comes from it running each measure in the switch whether it is used or not. I then read that if you use variables only the used one is evaluated but that is only partially true. If the variable is in the switch it will be run regardless if that branch with the variable is used but if the variable is not present in the switch at all then it is not evaluated.

I imagine I must be missing something obvious as this is basic logic control, no other programming language executes every line of the switch before deciding which one to use. The IF statement seems to have the same issue. How does one set up a MTD/YTD switch without a 3 fold slowdown and how come people aren't howling for a fix?

Thanks

AndrewZ

[parry2k]

@AndrewZ I will not store value in option1 or option2 since these are not getting used twice. Regarding your question, I have to do some testing at my end, maybe this is what the behavior is, which we can put forward to the product team but at least you have a little bit efficient and faster code 🙂

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@AndrewZ couple of general improvements:

Organic Revenue Growth % (Actual) Mth / YTD =


SWITCH ( 
SELECTEDVALUE ( 'ZZ_Switch Mth YTD'[Periodic / YTD], "YTD" ) ,
"Periodic" , [Organic Revenue Growth % (Actual)],
"YTD" , [Organic Revenue Growth % (Actual YTD)]
) 

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[AndrewZ]

Hi

So this does work but strangely I tried it this this morning and it made no difference (December update?)

IF(selectedVALUE('ZZ_ITY and ARO YTD_FY Switch'[YTD / FY])= "YTD" , option1, option2)

but now it works much faster. I know hasonevalue is slow itself but why does it make each line in the switch evaluate?

[AndrewZ]

Ok no the if statement doesn't work I was looking at the wrong table. Why does the switch suddenly work as expected when using selected value this way. Why does the if statement not work?

[AndrewZ]

As I was checking the versions numbers it updated to December 2021 which seems to have improved performance for the switch but it still evaluates each line. So for instance this takes 4 seconds on average because 2 measures appear in the measure reagardless of weather YTD or Periodic is selcted

Organic Revenue Growth % (Actual) Mth / YTD =

var option1 = [Organic Revenue Growth % (Actual)]

var option2 = [Organic Revenue Growth % (Actual YTD)]

RETURN

IF(HASONEVALUE('ZZ_Switch Mth YTD'[Periodic / YTD]) ,

SWITCH(VALUES('ZZ_Switch Mth YTD'[Periodic / YTD]) ,

"Periodic" , option1 ,

"YTD" , option2) ,

option1)

This takes 2 because I've made option2 equal to the same thing as option1. I would get the same if I used option1 for each switch return.

Organic Revenue Growth % (Actual) Mth / YTD =

var option1 = [Organic Revenue Growth % (Actual)]

var option2 = [Organic Revenue Growth % (Actual)]

RETURN

IF(HASONEVALUE('ZZ_Switch Mth YTD'[Periodic / YTD]) ,

SWITCH(VALUES('ZZ_Switch Mth YTD'[Periodic / YTD]) ,

"Periodic" , option1 ,

"YTD" , option2) ,

option1)

[parry2k]

@AndrewZ this is weird. You did an in-depth analysis and seems very strange. Which desktop version you are using? The reason I asked, a few months ago there was a release in which the switch function was improved and I'm wondering if it has something to do.

[AndrewZ]

You know its odd that I thought it always updated to the latest version but I see its Version: 2.99.862.0 64-bit (November 2021). Lets see if a version change improves it but I believe its the same on service. And yes we've had a massive slowdown in the last month so it might have been the October update.