In this article, we will discuss dynamic multiple column selection using a slicer and a button in Microsoft Power BI. Here, we will use the new page navigation option which is provided action property of the button. Page Navigation in action property is available from Power BI March 2020 update. We have already explained it through an article, you can check it to know more about it.
Let`s see How to do Dynamic Multiple Column Selection in Power BI?
1. Create a table with the column 'Column Names' which has all the columns to be placed in the slicer and on the basis of which data need to be analyzed in the Pivot Table.
Click on the Enter Data button and create a table with a column having the column names as shown in the above image.
2. Create a slicer with all the columns to be shown in the pivot table.
3. Create Pages for every possible combination of selection in the slicer. Here, For the demo, I have taken three combinations based on two value selection in the slicer. For two value selection, In the real-life scenario, you can have a total of 6 combinations.
As you can see in the above image, the slicer is synced on all pages and also visible.
5. Create a button and pivot table in all the pages. Make sure in the Pivot table column selection is done as per the Page. For Region+Segment Page, use Region column and Segment in the row headers.
VALUES ( 'Columns'[Column Names] ),
CONCATENATEX function retrieves the value from column row by row and then combine them with delimiter mentioned in the formula
When the Region and Segment are selected in the slicer, the 'Columns'[Column Names] is filtered based on the slicer selection. So we have only 2 values in the 'Columns'[Column Names] i.e Region and Segment.
With the CONCATENATEX function, we are able to combine the column names with + placed in between them.
7. We have also created a separate measure that will make button text dynamic based on slicer selection. DAX expression is mentioned below.
Go to Button =
"Go to " & [Page Selection] & " Page"
So, String concatenation has been done in the above DAX expression. [Page Selection] measure which we have created in the 6th step will give the exact page name.
8. Now we have to place the measure Go to Button in the conditional formatting using field option in Button Text Property.
- Click on the button and go to Visualization pane and click on fx in the button text property.
9. Now we have to go to Action property and select the Page navigation in the Type option. In the Destination option, Click on the fx button to use the Page Selection measure in the conditional formatting using the field to set the destination dynamically based on Page Selection measure.
10. Create a new page, Main Page, and copy the slicer and button to the Main Page. Make sure to sync the slicer in the Main Page as well.
11. Hide all the pages apart from Main Page. You will get a hide page option when you right-click on the page.
Once you publish your report in Microsoft Power BI Service, you will have only one page there and after slicer selection, you can click on the button to redirect it to hidden pages but their name will not be shown only appropriate pivot table will be shown.
Final ThoughtsUsing the Page Navigation option, you can accomplish the dynamic multiple column selection scenario very easily. This is suitable when there is less number of selection combinations. However, you can use it when you have large number of selection combination but you have to make lot of pages depending upon selection combination.
Please let me know you feedback in the comment section. If you need a pbix file for this scenario then please post your email id in the comment. Actually, I did not find an option to upload the pbix file.