Performance when User Selected Periods (SWITCH + SELECTEDVALUE)

[Kaycee]

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.

[Anonymous · Accepted solution]

Hi @Kaycee ,

It seems difficult to improve. But you can improve performance in other ways.

1.Turn off auto date/time

2.Remove unnecessary columns

3.Optimizing the data model

4.Reducing the number of values but not the information

5.Reducing precision

6.Choosing measures over calculated columns

To learn more details ,you could read the following articles:

10 Best practice tips to improve your Tabular Model performance

HOW TO IMPROVE POWER BI PERFORMANCE - PART I
HOW TO IMPROVE POWER BI PERFORMANCE - PART II
HOW TO IMPROVE POWER BI PERFORMANCE - PART III

Wish it is helpful for you!

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.