Slicers in Microsoft Power BI improve the end-user experience by providing a quick way to filter data in a meaningful way.
Most Microsoft Power BI visualizations in the same report represent related data. Each visualization is specialized, while the overall view of the report provides a higher perspective. For example, you might want to view sales by region and by staff. Using a slicer, your end users can simultaneously compare region and staff sales across different visualizations with a quick click. Slicers are easy to implement in the design phase and, above all, easy for the end users.
In this tutorial, I’ll show you how to add a simple slicer to a report in Power BI. A slicer is just another way to filter and summarize data across multiple visualizations.
TO SEE: Google Workspace versus Microsoft 365: a side-by-side analysis with checklist (Tech Republic Premium)
I’m using Power BI on a Windows 10 64-bit system. You can download the demonstration .pbix file, AdventureWorks Sale of GitHub. In particular, we will work with the Sales and Sales Area tables. Once downloaded, double-click the .pbix file to open it in Power BI and follow or use your own .pbix file.
What are slicers in Power BI?
Slicers are a filtering method that displays different options, usually for multiple visualizations in the same report. If you are familiar with Microsoft Excelyou are probably already familiar with slicers and will use them in the same way.
There are many situations where you can use slicers in Power BI:
- When you want to provide end users with more than one filtering option.
- When end users want to filter without opening a dropdown to access choices.
- When end users want to filter on columns that are not in the visualizations.
You will find different types of slicers:
- Numerical Range Cutting Machines: Show values between, less than or equal to a number, or greater than or equal to.
- Relative date cutters: Show values within a specified date period.
- Relative Time Cutters: Show values within a specified time period.
- Responsive, adaptable cutting machines: Resize it to fit any space in your report.
- Multi-field slicer hierarchy: Filter multiple related fields in one slicer.
We need some visualizations before we can build the slicer. We’ll keep it simple for demonstration purposes, but most likely your visualizations and reports will be more complex.
Create visualizations in Power BI
The slicer shown in Image A is a simple list of regions. There are three visualizations and all three are updated with the slicer. The bar chart at the top compares total sales by region. The map visualization on the left shows total profit and the one on the right shows total sales by region.
Image A
To create the clustered bar chart, click the clustered bar chart thumbnail in the Visualizations pane. Then click the following fields in the Fields pane: Region in the Sales Territory table and Sales Amount in the Sales table.
To add the map visualizations, click the map thumbnail in the Visualizations pane. Then click Profit Amount in the Sales table. For the second map visualization, click Sales Amount in the Sales table.
Resize and position the three visualizations with Image A as a guide. Or use your own configuration.
Now that the visualizations are in place, it’s time to add the slicer.
Add a slicer to a report in Power BI
For this simple report, we want a slicer that filters all three visualizations by region. The Power BI slicer is a simple list of regions (Image A). To add a slicer to the report, do the following:
1. Click Slicer in the Visualizations pane. Power BI adds an empty slicer frame.
2. In the Fields pane, expand the Sales Region table and check Region (Figure B).
Figure B
3. Size and position of the cutting machine.
You may be wondering how the slicer can work if the two map visualizations don’t include the Region field. This bit of magic works because the tables are related. When working with your own data, this is an important step that you should not miss. The tables must have the correct relationships for the slicer to work as expected.
Click the Model icon to see the relationships. Figure C shows an existing relationship between the Sales and Sales Area tables. This relationship allows the slicer to filter all three visualizations by region, even though the region field is not in either map visualization.
Figure C
If you look closely at the slicer, you’ll see a non-region entry, Corporate HQ. This item should not be in the slicer, so right click on it and choose Exclude from the resulting submenu.
It’s time to use the slicer to see the benefits of including a slicer in a Power BI report.
Using a slicer in Power BI
Using a slicer in Power BI is no mystery: just click on the item you want to filter across all three visualizations. But first you need to know how to clear the slicer. Select the slicer and you will see a few small icons in the top right corner. Click on the one that looks a bit like an eraser (Figure D) to return all three visualizations to their original state, which evaluates all regions.
Figure D
Now let’s use the slicer to filter all three visualizations by checking Germany in the slider. As you can see in Digits Eall three visualizations are updated accordingly.
Digits E
Unfortunately, the clustered bar chart isn’t useful if only one region is selected, so hold down the Ctrl key and check Australia. With two bars, the visualization compares two different regions (Figure F). The two cards represent the sum of both regions.
Figure F
Adding a slicer in Microsoft’s Power BI is simple and sensible if you want to give end users detailed insight into the data.
