Forum Discussion

Kaycee's avatar
Kaycee
Advocate I
3 years ago
Solved

Performance when User Selected Periods (SWITCH + SELECTEDVALUE)

Hi all, 

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 = 
    SELECTEDVALUE(
        '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
    )

RETURN 
_Result

 



Step 3 - Write a Measure to conditionally change the period based on Selection

 

 

Act P&L PeriodSelect = 
    SWITCH([Period Selected], 
        "Month", [Act P&L MTH],
        "Year to Date", [Act P&L YTD],
        "Prior Year YTD", [Act P&L Last YTD],
        "Pre-Covid YTD", [Act P&L YTD Pre-Covid],
        "Full Year", [Act P&L FY],
        "Prior Year", [Act P&L Last FY],
        BLANK()
    )

 


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.

1 Reply