To create a single visual that allows users to select the table's data they want to use based on a slicer, you can utilize Power BI's query parameters. Here's how you can achieve this:
Open Power Query Editor from the Home tab in Power BI Desktop.
Create a new query by selecting "New Source" and choosing "Blank Query".
In the formula bar, define the following query to retrieve the list of table names:
TableNames = {"Question1", "Question2", "Question3"} // Replace with actual table names
Modify the list of table names to match your specific table names. You can add as many table names as required.
Close and apply the changes to create the query.
Now, go to the "Home" tab and click on "Manage Parameters".
In the Manage Parameters dialog box, click on "New Parameter" to create a new parameter.
Configure the parameter with the following settings:
- Name: SelectTable
- Data type: Text
- Allowed values: List of values
- Suggested values: TableNames (select the "TableNames" query you created earlier)
- Current value: Question1 (or any default table name you prefer)
Click OK to create the parameter.
Next, create a new query by selecting "New Source" and choosing "Blank Query".
In the formula bar, define the following query to retrieve the selected table based on the parameter:
SelectedTable = Table.SelectRows(Excel.CurrentWorkbook(){[Name=Parameters[SelectTable]{0}[Value] & "_Table"]}[Content], each true)
This query uses the parameter value to dynamically select the table by appending "_Table" to the parameter value.
Close and apply the changes to create the query.
Now, you can create your visual (e.g., a table or chart) using the "SelectedTable" query as your data source.
Add a slicer visual to your report canvas and select the "SelectTable" parameter as the field for the slicer.
When users interact with the slicer, the visual will automatically update to display the data from the selected table.
By using query parameters, you can dynamically switch between different tables and have a single visual that adapts to the user's selection.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
