Dynamic Multiple Column Selection in Power BI
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 the action property of the button. Page Navigation in action property is available from Power BI March 2020 update.
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.
We will be selecting two values in the slicer just for demo purposes though 3 or 4 value selection is possible.
3. Create Pages for every possible combination of selections in the slicer. Here, For the demo, I have taken three combinations based on two value selections in the slicer. For two value selections, In the real-life scenario, you can have a total of 6 combinations.
4. Sync the slicer on all the 3 pages so that the values selected remain the same on another page.
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 on all the pages. Make sure in the Pivot table column selection is done as per the Page. For Region+Segment Page, use the Region column and Segment in the row headers.
6. Now we need to create a measure to capture the multiple values selected in the slicer and make it exactly similar to the page name. I have created a Page Selection measure for the same. Its DAX expression is mentioned below.
Page Selection =
CONCATENATEX (
VALUES ( ‘Columns'[Column Names] ),
‘Columns'[Column Names],
“+”
)
CONCATENATEX function retrieves the value from column row by row and then combines them with the delimiter mentioned in the formula
When the Region and Segment are selected in the slicer, the ‘Columns'[Column Names] are 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 the field option in Button Text Property.
- Click on the button and go to the Visualization pane and click on fx in the button text property.
- Select the measure Go to Button in the Based on the field section.
9. Now we have to go to the 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 the 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 on the Main Page as well.
Final Thoughts
Using the Page Navigation option, you can accomplish the dynamic multiple column selection scenarios very easily. This is suitable when there is less number of selection combinations. However, you can use it when you have a large number of selection combinations but you have to make a lot of pages depending upon the selected combination.
Please let me know your 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.
[email protected]
[email protected]