Question

How do I dynamically switch measures in Power BI based on a slicer selection while maintaining performance?
I need to create a Power BI report where users can switch between different measures dynamically using a slicer while ensuring optimal performance. What is the best approach using DAX functions like SWITCH or disconnected tables to efficiently switch between measures?

Answer 1

To dynamically switch between different measures in Power BI using a slicer while maintaining optimal performance, one would adopt the following approach:

Steps to Implement Dynamic Measure Switching

Create a Disconnected Table for Measure Selection

In Power BI Desktop, create a table with the names of measures.

Example

MeasureSelection = 
DATATABLE(
    "MeasureName", STRING, 
    { 
        { "Total Sales" }, 
        { "Total Profit" }, 
        { "Sales Growth %" }
    }
)

This table acts as a slicer source.

• Create the Dynamic Measure Using SWITCH
• Define a measure that evaluates based on the selected slicer value:

Selected Measure = 
VAR SelectedMeasure = SELECTEDVALUE( 'MeasureSelection'[MeasureName] )
RETURN SWITCH(
    SelectedMeasure,
    "Total Sales", SUM( 'Sales'[SalesAmount] ),
    "Total Profit", SUM( 'Sales'[Profit] ),
    "Sales Growth %", 
        DIVIDE( SUM( 'Sales'[SalesAmount] ) - SUM( 'Sales'[SalesAmount LY] ), SUM( 'Sales'[SalesAmount LY] ) ),
    BLANK()
)

SWITCH() evaluates one measure at a time by design to optimize performance. Next, SELECTEDVALUE() will get the selected slicer option. 
Applying the Measure to Your Visuals 

Next, assign the 'MeasureSelection' table as a slicer. For charts/tables, apply the Selected Measure measure to project user choices.