Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kaycee
Advocate I
Advocate I

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

Kaycee_0-1667960669569.png


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 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
v-stephen-msft
Community Support
Community Support

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.