Performance when User Selected Periods (SWITCH + SELECTEDVALUE)
I am building a report that allows users to select the period being reported. This will go into a P&L style matrix which may contain many values (departments + Act/Bud/Var in columns; account groupings on rows).
To achieve this I have created 3 steps.
Step 1 - Create a Table for the Period Slicer
Step 2 - Create a Measure to Identify the Period Selected
Period Selected =
VAR _Default = "Year to Date" -- Value to be applied if a period has not been selected
VAR _Result =
'Apply Period Selection'[Select Period], -- Checks if there is a single value in [Period Selection]; returns that value if so
_Default -- If not a single value (ie. no filter applied); returns the _Default value defined above
Step 3 - Write a Measure to conditionally change the period based on Selection
I have used this many times and it is fine. However, typically I use it in visuals that have a relatively small number of results. However as mentioned earlier, this particular P&L has a substantial number of individual values. The small performance issues of using a SWITCH statement is heavily exaggerated. For reference, the loading time when using the SWITCH statement vs using a single measure (ie. Just using [Act P&L MTH]) can be as much as double the processing time when loading the visual.
Are there any alternatives that would improve performance in this scenario?
@amitchandak tagging you in as well if you have a moment - I noticed you've answered qns with a similar theme in the past. TIA.