How to Add Slicer in Power BI Report Builder

Adding a slicer in Power BI Desktop is a quick drag-and-drop, but when you open Power BI Report Builder, you might notice the familiar slicer visual is nowhere to be found. This is because Report Builder is designed for paginated, pixel-perfect reports, not interactive dashboards. This article will show you the standard method for creating slicer-like functionality in Power BI Report Builder using a tool called parameters, giving you the power to filter your reports interactively.

Why Is There No Slicer in Power BI Report Builder?

Before we build the solution, it helps to understand why Power BI Report Builder and Power BI Desktop work differently. It’s not an oversight, it’s by design.

• Power BI Desktop is for Exploration: It’s designed for interactive data exploration. Slicers, cross-filtering, and drill-downs are core to this experience, allowing users to freely slice and dice data on a dynamic canvas.
• Power BI Report Builder is for Paginated Reporting: This tool is built to create “paginated” reports - think sales invoices, detailed financial statements, or inventory lists that are formatted precisely page by page. These are often exported as PDFs or printed, so the focus is on a structured, static layout rather than freeform interaction.

In the world of paginated reports, the tool used to filter data isn't called a "slicer." It's called a parameter. By setting up parameters correctly, you can create dropdown lists that function almost exactly like slicers, letting users choose values to filter the report before it's displayed.

Creating a Parameter to Act as a Slicer

The entire process comes down to three main stages: creating a dataset to populate our filter options, setting up the parameter itself, and finally, telling our main report data how to respond to that parameter. Let's walk through it with a practical example, like filtering a sales report by product category.

Step 1: Set Up Two Datasets

For a parameter-based slicer to work, you need at least two datasets configured in the Report Data pane:

1. The Main Data Dataset: This is the query that pulls all the data for your report (e.g., sales transactions, product details, quantities, and dates). We'll call ours SalesData.
2. The Parameter Values Dataset: This is a new, separate dataset whose only job is to provide the list of unique values for your slicer dropdown. For example, if we want to filter by product category, this dataset will be a simple list of all unique categories (e.g., "Clothing," "Accessories," "Equipment"). We'll call this ProductCategories.

Why use a separate dataset for the parameter values? It keeps your report dynamic. When a new product category is added to your database, your "slicer" will automatically update with the new option without you having to manually edit a static list.

How to Create the Parameter Values Dataset:

In the Report Data pane on the left, right-click on Datasets and select Add Dataset...

Let's assume you're using a Power BI dataset as your source. In the query editor, you would write a simple DAX query to get a distinct list of product categories.

EVALUATE
DISTINCT('Product'[Category])

Name this dataset ProductCategories and click OK. Now you have a list ready to power your slicer.

Step 2: Create the Report Parameter

With our list of categories ready, we can create the interactive dropdown. In the Report Data pane, right-click the Parameters folder and choose Add Parameter...

This opens the Report Parameter Properties window. You’ll need to configure a few key tabs:

1. General Tab

• Name: Give the parameter a concise name with no spaces (this is its internal ID). Example: CategoryParam
• Prompt: This is the user-friendly text that will appear next to the dropdown menu. Example: Select a Product Category:
• Data type: Choose the type of data in your list. For product categories, this would be Text.

2. Available Values Tab

This is where you connect your dropdown list to the dataset you created in the previous step.

• Select the option Get values from a query
• Dataset: Choose your parameter values dataset (ProductCategories)
• Value field: Select the field from that dataset containing the value (e.g., [Category])
• Label field: This is what the user sees. In most cases, it will be the same as the value field (e.g., [Category])

3. Default Values Tab (Optional)

You can set a default value so the report is already filtered when the user first opens it. Like with Available Values, you can specify a static value or get one from a query. For now, we can leave it as No default value

Once you're done, click OK. You've now officially created the parameter!

Step 3: Connect the Parameter to Your Main Dataset

At this point, you have an interactive dropdown, but it doesn't do anything yet. The final, critical step is to tell your main report data (SalesData) to pay attention to what the user selects in that dropdown.

Here’s how you link them:

1. In the Report Data pane, right-click on your main dataset (SalesData) and choose Dataset Properties.
2. Go to the Filters tab on the left.
3. Click the Add button to create a new filter rule.
4. This adds a new row where you'll define the logic:

• Expression: Click the expression builder button (fx). In the window that opens, select the field from your main dataset that you want to filter. For our example, this would be the [Category] field.
• Operator: Choose the comparison. Since we want an exact match, leave it as the equals sign (=).
• Value: This is where you connect to the parameter. Click the fx button. In the expression window, go to the Category pane, choose Parameters, and double-click the parameter you created ([@CategoryParam]).

Your finished filter should read: [Category] = [@CategoryParam].

Click OK to close the properties windows.

Step 4: Run and Test Your Slicer

That's it! In the Home ribbon, click the Run button to preview your report. At the top of the report view, you will now see your prompt - "Select a Product Category:" - alongside a dropdown menu populated with all your product categories.

Select a category from the list and click the View Report button. The table or visual in your report will instantly re-render, showing only the data for the category you chose. You’ve successfully created a slicer in Power BI Report Builder.

Advanced Tips and Tricks

Once you've mastered the basics, you can enhance your parameter slicers with more advanced features.

Creating a "Multi-Select" Slicer

What if you want to let users select multiple categories at once? It's a simple change:

1. Go back into the properties for your parameter (CategoryParam).
2. On the General tab, check the box for Allow multiple values.
3. Now, adjust the filter on your main dataset (SalesData). Change the Operator from = to In.

The In operator allows the filter to check if the category is within the list of values the user selected.

Adding a "Select All" Option

Users often want an "All" option to quickly see the unfiltered view. This takes a bit of clever query work.

1. Modify Your Parameter Dataset: Edit the query for your ProductCategories dataset to include a row for "All." A UNION statement is perfect for this.

--For a SQL data source
SELECT DISTINCT [Category] FROM Products
UNION ALL
SELECT 'All'

2. Adjust the Filter Logic: An "All" value will break your filter because there's no category named "All" in your data. To fix this, you need to use a conditional expression for your filter's value. You can set the filter to ignore the parameter when its value is "All." This requires an expression-based filter, which is slightly more advanced, but provides a much better user experience.

Final Thoughts

While Power BI Report Builder lacks the dead-simple slicer visual found in Power BI Desktop, you can replicate its filtering power using parameters. By setting up a dedicated dataset for your filter values, creating a parameter linked to that data, and applying a filter to your main report, you can give users the ability to slice and dice your paginated reports interactively.

Tools like Report Builder are obviously very powerful, but the learning curve - understanding the nuances of datasets, parameters versus slicers, and DAX expressions - is steep. At Graphed, we built our platform to eliminate this exact kind of friction. Instead of a 10-step process, you can simply ask, "show me sales for last month, and create a filter for product category." We handle connecting the data, creating the controls, and building the dashboard in seconds. This allows you and your team to focus on the insights from your data, not on the tedious process of getting them.