How to use slicers to choose columns in a table?

You cannot directly use standard Power BI slicers to dynamically change which columns appear in a visual, because visuals require a fixed schema defined at design time. However, you can achieve this effect by creating a disconnected table of column names and using DAX measures with the SELECTEDVALUE function to return data for the chosen field. This technique allows users to toggle between different metrics or attributes in a single visual, such as switching a card from showing "Sales" to "Profit" based on their selection.

Understanding the Limitation

Power BI visuals like tables, matrices, and cards rely on a static structure. When you drag fields into the Columns or Rows well of a matrix, those fields become part of the visual's definition. A slicer connected to your data model filters rows (data records) but does not alter the columns (fields) displayed. If you try to drag a slicer field directly into the Values well alongside other fields, it will simply add another column rather than replacing the existing ones. Therefore, dynamic column selection requires a workaround that changes the output of a measure based on user interaction, rather than changing the visual layout itself.

Step 1: Create a Disconnected Column List

The foundation of this solution is a lookup table that contains no relationship to your main data model. This table acts purely as a selector. You can create this manually or via DAX. Create a new table named ColumnSelector. It should contain a single column listing the exact display names of the fields you want to switch between. For example, if you have a Sales table with Total Sales, Total Profit, and Units Sold, your ColumnSelector table should look like this:

ColumnSelector = DATATABLE(
    "Field Name", STRING,
    {
        {"Total Sales"},
        {"Total Profit"},
        {"Units Sold"}
    }
)

Do not create any relationships between ColumnSelector and your fact tables. This disconnection ensures that selecting an item in the slicer does not filter your underlying data, only the calculation logic.

Step 2: Build Dynamic Measures

Next, you need to create measures that read the user's selection from the slicer and return the corresponding value. Use the SELECTEDVALUE function, which returns the single value selected in the slicer, or a blank/default if multiple or no items are selected. Create a new measure called Dynamic Value:

Dynamic Value = 
VAR SelectedField = SELECTEDVALUE('ColumnSelector'[Field Name])
RETURN
SWITCH(
    TRUE(),
    SelectedField = "Total Sales", [Total Sales],
    SelectedField = "Total Profit", [Total Profit],
    SelectedField = "Units Sold", [Units Sold],
    BLANK()
)

In this code, [Total Sales] etc., refer to pre-existing measures in your model. The SWITCH function evaluates the selected text and returns the result of the matching measure. If the user selects nothing or an invalid option, it returns blank.

Step 3: Configure the Visual

Now, build your visual using the dynamic measure instead of individual fields. 1. Add a Card visual (or a Table if you are grouping by a dimension like Date). 2. Drag the Dynamic Value measure into the Values well. 3. If using a Table visual, you may also need to add a dimension field (like Date) to the Rows well so the data has context. 4. Add a Slicer visual to the report page. 5. Drag the ColumnSelector[Field Name] column into the slicer's Field well. When you click different values in the slicer, the card or table updates to show the metric you selected. Note that for Matrix visuals, this approach works best for the Values area. You cannot easily swap entire row/column headers dynamically using this method without complex DAX involving CROSSJOIN or unpivot techniques, which often degrade performance.

Best Practices and Performance Tips

This pattern is efficient because it uses simple measure evaluation rather than complex query folding operations. However, keep these considerations in mind:

• Consistency: Ensure the strings in ColumnSelector exactly match the logic in your SWITCH statement. Typos will result in blank outputs.
• Formatting: Apply consistent number formatting to all measures referenced in the SWITCH statement. If one measure is formatted as currency and another as percentage, the visual may look inconsistent when switching.
• Alternative for Rows: If you need to switch row headers, consider using a parameter table similar to the one above, but combined with CALCULATE and UNION to reshape data, though this is more resource-intensive.

For more information on managing Excel table structures, you might find Summarizing max. values for each row in an excel table useful for understanding how to handle dynamic aggregations in other tools.

Frequently Asked Questions

Can I use this method to hide/show entire columns in a Matrix?

No, standard slicers cannot dynamically add or remove columns from a Matrix visual's schema. This technique only changes the value displayed in a single measure slot. To show/hide columns, you must manually configure the visual or use bookmarks to switch between pre-defined layouts.

What happens if I select multiple items in the slicer?

The SELECTEDVALUE function returns blank if more than one item is selected. To handle multi-select scenarios, you would need to modify the DAX to use VALUES and iterate through them, but this complicates the logic significantly and is generally not recommended for simple column switching.

Does this work with DirectQuery mode?

Yes, this DAX pattern works in DirectQuery mode because it relies on measure evaluation rather than data transformation. However, ensure that the base measures ([Total Sales], etc.) are also optimized for DirectQuery to avoid performance bottlenecks.

How do I format the numbers correctly?

Apply formatting to each individual measure (e.g., [Total Sales] set to Currency) before using them in the SWITCH statement. The dynamic measure inherits the format of whichever measure is returned. Ensure all possible outcomes have compatible formats to avoid visual confusion.

Is there a limit to how many columns I can switch between?

Technically, no, but performance may degrade if you have dozens of complex measures in the SWITCH statement. For large numbers of options, consider unpivoting your data model into a long format, which allows native filtering of attributes without custom DAX.