Forum Discussion

RSchenk's avatar
RSchenk
New Member
3 years ago

Trouble loading data on a table using SWITCH measure

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!

Need Help