Slicers based on dynamically selected columns

[Anonymous]

I am working on a table that has many columns. I need to build slicers that can work with dynamically selected columns.

For example, consider I have 100 columns named as follows: column_1, column_2, column_3... column_100. I want to build a report where:
1a. I build a slicer to pick one of the hundred columns

1b. Build another slicer to help filter values based on the column selected in Step 1a.

2a. I build a slicer to pick one of the remaining 99 columns

2b. Build another slicer to help filter values based on the column selected in Step 2a.

And so on..

How can I achieve this in Power BI?

Thank you!

[123abc]

To achieve dynamic column selection and filtering in Power BI, you can follow these steps:

1. Modeling Data: Ensure that your data is properly modeled in Power BI, with all 100 columns loaded into your dataset.

2. Create Parameters: Create two parameters to store the selected column names. Go to Home > Manage Parameters > New Parameter. Create two parameters: Column_Selection_1 and Column_Selection_2.

3. Create Slicers: Create slicers for each parameter you've created:

   • Go to the Visualizations pane.
   • Drag the desired parameter (Column_Selection_1 and Column_Selection_2) into the report canvas.

4. Create Calculated Columns: Create calculated columns that reference the selected parameters. These calculated columns will be used for filtering your data.

   For example, if your original column is named column_1, you can create a calculated column like this:

Filtered_Column_1 =
SWITCH(
TRUE(),
'Table'[Column_Selection_1] = "column_1", 'Table'[column_1],
'Table'[Column_Selection_1] = "column_2", 'Table'[column_2],
...
'Table'[Column_Selection_1] = "column_100", 'Table'[column_100],
BLANK()
)

1. Repeat the process to create Filtered_Column_2 for the second selected column.

2. Apply Filters: Use the calculated columns (Filtered_Column_1 and Filtered_Column_2) as slicers for filtering your data.

3. Repeat for Additional Selections: Repeat steps 2-5 for the second set of slicers (columns 2a and 2b).

This way, you can dynamically select columns using parameters and filter your data based on the selected columns. Ensure your DAX expressions cover all the columns you have in your dataset and adjust them accordingly if your column names or number of columns change in the future.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous · Accepted solution]

Thanks @123abc. That can work but today I found another solution that can be quicker to implement. Essentially, we can go in Modeling>New parameter and can create a dynamic slicer by selecting the relevant columns from the table. Found this video with step by step instruction: Power BI: Create Dynamic Slicers Using Field Parameters (youtube.com).