Trouble loading data on a table using SWITCH measure

[RSchenk]

Hi everyone,

I have a problem I am not sure how to solve. I use a switch measure in my report in several pages. I have to use it on a Table visual with the "Show items with no data" option enabled.

My switch is rather simple:

Switch Survey Count = SWITCH (

    SELECTEDVALUE ( 'Switch NPS'[Name] ),

    "Selected Period", [Survey Count],

    "12 Months Rolling", [Survey Count 12 MO],

    "YTD", [Survey Count_VAL],

    [Survey Count]

)

If I use the above measure in the table and select "Selected Period" on a slicer of the 'Switch NPS'[Name] field (Switch NPS being an unrelated table), it keeps on buffering and never loads the data.

It does however load the data very quickly (as expected, as the model has only <100k row in the main fact table) if:

• the table has the "Show items with no data" option disabled

OR

• there is no selection on the 'Switch NPS'[Name] slicer so the SELECTEDVALUE in the switch evaluates to blank and ultimately returns the same measure it would if "Selected Period" was selected.

OR

• I use the underlying measure ([Survey Count])

So the issue is that the SWITCH cannot evaluate the SELECTEDVALUE on the unrelated table Switch NPS's [Name] field.

What can cause this horrendous slow down in performance and how can I fix it? I need the users to be able to switch between the measures.

Many thanks!

[amitchandak]

@RSchenk , option one explore field parameters if that can help

Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...
Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE

if you want the same way, move the measure calculation in place of measure names and try