Do you want to add a dropdown menu to your Power BI reports?
Power BI empowers users to create dynamic and interactive dashboards to analyze and present data effectively. Dropdown menus are a valuable tool in Power BI that allows users to interact with data and customize their visualizations.
The dropdown menu feature enables users to select specific values from a list, facilitating easy data filtering and enhanced data exploration.
In this article, you will delve into the step-by-step process of creating dropdown menus in Power BI and various aspects of it.
Adding Dropdown to Power BI Visual through Slicers
For this article, I used the fictitious SuperStore_Sales data, which I have been using for quite some time now.
- Open the Power BI Desktop app
- Go to the Home tab.
- Click on the Get Data dropdown from the Home tab.
- Select the option Excel workbook.
- Navigate to the path where the Superstore_Sales.xlsx file is stored.
- Load the SuperStore_Sales table from this file to the tool so that you can use it for this demo.
- Go to the Visualization pane.
- Click on the Slicer visual to add an interactive filtering slicer to your report page.
Once the slicer visual is added to the reporting canvas, it demands a field based on which the values will be filtered interactively. In this case, you can add the Sub-Category field.
- Check the tick-box next to the field to add inside the Data pane. Alternatively, you can drag and drop it to the Field section in the Visualization pane.
Now, you will see that each unique value belonging to the Sub-Category field is added in the slicer, and it also has a square radio button next to their names so that users can click here and select one or more of them.
Until the recent Power BI updates, you could change the slicer style to a dropdown through the slicer itself, where a drop-down arrow is placed beside the eraser button that allows users to change the slicer’s appearance from a List to a Dropdown and vice versa.
You can also see a message box there suggesting you go to the new method to customize your slicer.
Here’s how to change the slicer style to a dropdown list,
- Go to the Format visual section from the Visualizations pane.
- Expand the Slicer settings option placed in the Visual section.
- Click Style under the Options dropdown.
- Select the Dropdown from the list of options.
Check the appearance of the slicer on the report canvas now, and it will be converted into a Dropdown list rather than a Vertical list.
To test this Dropdown list, let’s add another Clustered bar chart visual to the Power BI, with Sub-Category on the Y-axis and Sales on the X-axis.
Now, if you select any one Sub-Category from the dropdown, you will also see that the Clustered bar chart visual is being altered based on that filtered selection.
Selecting Multiple Values from the Dropdown
Often, users tend to be confused with this multi-value selection functionality and complain that they cannot select multiple values to slice the data through the Dropdown list.
The only reason you might not be able to do so is because you haven’t enabled (or kept On) the Muti-select with CTRL option.
- Select the Slicer visual.
- Go to the Format visual section from the Visualizations pane.
- Click on the Slicer settings inside the Visual section.
- Expand the Options dropdown and then the Selection dropdown within it.
- Click on the Multi-select with CTRL radio button to On this option.
This will allow you to select multiple values to slice the data using the Dropdown slicer.
Now, you can select multiple items in the dropdown by holding the Ctrl key while selecting values.
In the screenshot above, you can see that the Clustered bar chart is filtered for Appliances and Bookcases subcategories, respectively.
Enabling the Search option through the Dropdown menu
The dropdowns are so convenient that you always tend you use them while working with Power BI slicers. One of the features that should make them your go-to tool for slicing is the search box, which can be used to search for specific values from the dropdown list.
The search functionality within the dropdown slicers is more convenient when you potentially have a large set of values inside the dropdown list. There are two ways to enable the Search option inside the Power BI dropdown slicer.
The first way of Enabling the Search box in the Dropdown Slicer
- Click on the Sub-Cateogry dropdown slicer that you created previously.
- Go to the ellipses (three horizontal dots) placed at the right-hand side of this slicer visual and click on it to expand a list of options.
- Click on the Search option to enable the functionality through this visual.
Now expand the dropdown list, and you will see the Search box above the list of values to search for a specific value through the slicer.
This functionality is quite helpful for some lazy people (like me😜) who find it very hard to go through the entire list and instead prefer to search for the value by typing it through the search box.
The second way of Enabling the Search box in the Dropdown Slicer
This way is more of a shortcut to search through the slicer dropdown and is something that can be preferred when you don’t want to add the Search box permanently to your slicer.
Expand the dropdown in the slicer and click Ctrl + F from your keyboard to temporarily enable the search option for the current session inside your slicer.
Then, search for the values you like, and once you are done with it and click outside, the search box is gone.
Next time you open the dropdown, you will not see that search box there. However, you can again do Ctrl +
F to make it appear temporarily.
Dropdowns are a powerful feature in Power BI that enhances interactivity and usability.
By following the step-by-step guide outlined in this article, you can easily create dropdowns in Power BI and enable users to select specific values, filter data, and customize visualizations effortlessly.
You can convert your slicers to dropdowns through the Format visual section > Slicer settings > Options.
You can select multiple values while working with dropdowns in the Power BI slicer. This can be enabled with the Multi-select with CTRL option. set it up through the Format visual > Slicer settings > Selection.
Finally, you can enhance the dropdown capabilities by enabling the Search box, which can be very useful when you have long dropdown lists, making it tedious to scroll through them to find the value you need.
Do you know any other way to add a dropdown menu inside the Power BI report? Let me know through the comments section below!