Low Perfomance measure when using a Switch measure based on a slicer inside a table

[Anonymous]

Hi,
I am using a switch measure based on a slicer so that the users of my reports can choose which KPI they want to show inside a table between a large list of KPI.
Example : 

However as i add more measure inside my "KPI" measure my table is getting slower and slower to display.

For instance, I have two switch measure :
- "KPI 1 test" with 3 measures inside the switch
- "KPI 1" with around 60 measures inside the switch.

When I measure the performance to display my table with the two measure i get this result :
- "KPI 1 test" : 2,1second
- "KPI 1" : 4,5second

It seems that the lowest option you put in the switch the faster the measure will be..
Do you know if there is any posssibility to optimize the loading time of this formula while keeping the flexibility to have 60 measure inside the Switch ?

this is really an issue because on some others visuals it lead me to a loading time of 20 seconds 😕
Many thanks for your help
 

[Anonymous]

I had this same issue, and i'm afraid the way Power BI handles SWITCH & IF functions is it essentially runs the calculation on all options even if it doesnt meet the expressed criteria, so it does create performance issues.

I think calculation groups may be a good workaround for this, but it's something i'm just starting to look into myself

[smpa01]

@Anonymous  can you try the following optimization trick for SWITCH

Measure =
VAR _1 = [Measure1]
VAR _2 = [Measure2]
VAR _3 = [Measure3]
RETURN SWITCH (TRUE(), <condition1>,<_1>,<condition2>,<_2>,<condition3>,<_3>)

[Anonymous]

Thanks for your help.
I have tried your trick but unfortunatly it slightly increase the display time 😕

[m3tr01d]

Hello,

Switch/IF statement are normaly bad for performance when you have a table with a lot of rows. Do you show a lot of rows in the table?

Do you have the same bad performance with all the KPIs?

[Anonymous]

The table has 800K rows..

The performance vary a little bit depending on which KPI i choose, but there is always a big gap between a switch with few mesure and a switch with a lot of measure

[m3tr01d]

@Anonymous so you are showing to the use a Table with 800K rows?

[Anonymous]

@m3tr01d I misunderstood your question, this is my fact table that has 800K Rows.
The table I show has up 200 rows

[PaulOlding]

Wow, 60 measures to choose from!

I doubt this'll make much difference but on line 5 you're using VALUES.  That returns a table (with 1 row as you've tested for that in the IF).  You could try replacing it with SELECTEDVALUE, which returns a scalar.  Perhaps the evaluation of the SWITCH will speed up...

An alternative way of doing measure switching is with a calculation group.

You'd have a calculation item per measure.  The expression would just be the measure name

In the visual you'd use any measure, and have the calculation group as your slicer.

I don;t think there's any general rule that calculation group will be faster than switch or vice versa.  Just have to give it a try and see with your particular model & data.

[Anonymous]

Thank you PaulOlding for your help.
Unfortunatly the SELECTEDVALUE didn't impact the performance 😕

Ok i will dig with the calculation group in the coming days, i have never use Tabular Editor, but this is a good opportunity 🙂
I will keeep you updated with the results