How to add a slicer to a report in Power BI

Slicers in Microsoft Power BI improve the experience for end users 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 specializes, whereas the report’s overall picture gives a higher end perspective. For instance, you might want to view sales by region and by personnel. Using a slicer, your end consumers can compare region and personnel sales in different visualizations at the same time with a quick click. Slicers are easy to implement at the design stage and they’re especially easy for the end consumers to.

In this tutorial, I’ll show you how to add a simple slicer to a report in Power BI. A slicer is simply another way of filtering and summarizing data in multiple visualizations.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

I’m using Power BI on a Windows 10 64-bit system. You can download the demonstration .pbix file, AdventureWorks Sales from GitHub. Specifically, we will work with the Sales and Sales Territory tables. Once downloaded, double-click the .pbix file to open it in Power BI and follow along or use a .pbix file of your own.

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’re familiar with Microsoft Excel, you’re probably already familiar with slicers, and you’ll use them in the same way.

There are many situations when you’ll turn to slicers in Power BI:

• When you want to offer end users more than one filtering option.
• When end users will want to filter without opening a dropdown to access choices.
• When end users will want to filter by columns that aren’t in the visualizations.

You’ll find several types of slicers:

• Numeric range slicers: Show values between, less than or equal to a number, or greater than or equal to.
• Relative date slicers: Show values within a specified date period.
• Relative time slicers: Show values within a specific time period.
• Responsive, resizable slicers: Resize to fit any space on your report.
• Hierarchy slicers with multiple fields: Filter multiple related fields in a single slicer.

We’ll need a couple of visualizations before we can build the slicer. We’ll keep things simple for demonstration purposes, but most likely your visualizations and reports will be more complex.

How to create visualizations in Power BI

The slicer shown in Figure A is a simple list of regions. There are three visualizations and all three update with the slicer. The bar chart at the top compares total sales by regions. The card visualization to the left displays the total profit and the one to the right displays total sales by regions.

Figure 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 card visualizations, click the Card thumbnail in the Visualizations pane. Then, click Profit Amount in the Sales table. For the second card visualization, click Sales Amount in the Sales table.

Resize and position the three visualizations using Figure A as a guide. Or use your own configuration.

With the visualizations in place, it’s time to add the slicer.

How to add a slicer to a report in Power BI

For this simple report, we want a slicer that filters all three visualizations by the region. The Power BI slicer is a simple list of regions (Figure A). To add a slicer to the report, do the following:

1. Click Slicer in the Visualizations pane. Power BI will add a blank slicer frame.

2. In the Fields pane, expand the Sales Territory table and check Region (Figure B).

Figure B

3. Size and position the slicer.

You might be wondering how the slicer can work when the two card 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 can’t skip. The tables must have the proper relationships for the slicer to work as expected.

To see the relationships, click the Model icon. Figure C shows an existing relationship between the Sales and Sales Territory tables. Thanks to this relationship, the slicer can filter all three visualizations by regions, even though the region field isn’t in either card visualization.

Figure C

If you look closely at the slicer, you’ll notice a non-region item, Corporate HQ. This item shouldn’t be in the slicer so right-click it and choose Exclude from the resulting submenu.

It’s time to use the slicer to see the benefits of including one in a Power BI report.

How to use a slicer in Power BI

There’s no mystery to using a slicer in Power BI – simply click the item you want to filter all three visualizations. But first, you need to know how to clear the slicer. Select the slicer and you’ll see in the top-right corner a couple of small icons. Click the one that looks a bit like an eraser (Figure D) to reset 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 Figure E, all three visualizations update accordingly.

Figure E

Unfortunately, the clustered bar chart isn’t helpful with only one region selected, so hold down the Ctrl key and check Australia. With two bars, the visualization compares two different regions (Figure F). The two cards display the sum of both regions.

Figure F

Adding a slicer in Microsoft’s Power BI is easy and a reasonable thing to do when you want to provide end users with detailed insight into the data.