Blank Value in the Power BI Slicer and Filter

Have you ever been using Power BI and wondered why you can see a blank in the Power BI slicer and filter? You might wonder if you have done something wrong or if Power BI has added a blank as a default value. In actuality, seeing a blank in slicer or filter is a common problem in Power BI when you have some bad data in the data model. I will show you why this happens and how you can fix it.

You’ve designed good data model with a star-schema in Power BI:

Power BI data model with star schema.

You can start designing visuals by adding slicers and filters from the dimension tables. You may have seen the “(Blank)” value in slicer and filter, like the below screenshot:

Dimension tables highlighting Blank value in slicer and filter.

Why would there be a blank value in the slicer? The reason it appears is because of a data integrity issue, in other words; bad data. Is this something that can be fixed? Yes, but first you need to find the bad data by following the below steps.

Add No/ID from Fact table and Customer from dimension table:

Fact table and Customer in dimension table.

Click (Blank) from the slicer:

The word "Blank" highlighted in customer table.

Go to Power Query Editor. Find CustomerID from fact table and check the column Null or empty value:

"View" and "column quality" highlighted in Power Query Editor.

To fix the bad data, you need to go to the source. If that is not an option, you can fix it immediately and temporarily in Power BI. This can be done in both Fact and Dimension tables with a simple trick to remove the (Blank) value in slicer and filter by following the below steps.

1. Replace null value with your choice in the Fact table:

"Replace values" highlighted in CustomerID. Replace Value area shown in CustomerID. CustomerID highlighting"100%" under Valid.

2. Add a new row manually in the Dimension table:

  • Select the dimension table, then open advanced editor.
  • Add a line below to add a new line with the same Customer ID that you used in Fact table.
  • Note that if you have 10 columns in a dimension table, you need to add all 10 columns.

Dimension table showing CustomAddRow line.3. Click Done:

CustomerID highlighting "unknown" in table.

4. Click Close & Apply button, and you’re done!

Customer table showing basic filtering.

If you have any questions about using Power BI, please feel free to connect with us.

Get the Full Gartner Magic Quadrant for BI Report

Get the full report on the top BI Vendors.

Get the Report

Get the Gartner Magic Quadrant for BI Report

Get the Report