Power BI Filter a Slicer by Removing Entries With no Data

Using a slicer, you can turn your dashboard into an interactive experience, enabling your users to quickly filter different values.

If you use a dimensional model as the source of your Power BI model, chances are at some point you will want to use a dimension such as your Calendar, Customer, or Production dimension to in a slicer to filter data on a page. For the most part, that will work well, but what if your Calendar dimension contains many more years of data than your Sales fact, such as in the example below?

This example uses data you can download from the Adventure Works for Power BI data model provided by Microsoft at https://www.microsoft.com/en-us/download/confirmation.aspx?id=46801

In this example, you will notice the Sales table only has sales for years 2011, 2012, and 2013, but the Year Slicer shows years from 2005 to 2018. While this can work, in this post we will show you how to clean this up to only show the years which have sales associate to them.

Create a New Measure in your Calendar Table

Using the Calculate() function, we will create a measure in the Calendar table that sums up the Sales Quantity per date. QTYFilter = calculate(sum(Sales[SalesQuantity]))

Right-click the Calendar table and select New Measure.

Type in the formula for the metric you want to filter by. In this case we will use the Sales Quantity: QTYFilter = calculate(sum(Sales[SalesQuantity])).

Use the QTYFilter Measure to Filter the Year Slicer

Select the Year slicer.

In the Filters pane, drag the QTYFilter measure to the Filters on this Visual section.

In the Drop Down box, select is not blank, and click Apply filter.

As you can see, the Year slicer now only contains years where there was a sales quantity, giving you a much cleaner slicer.