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:
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:
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:
Click (Blank) from the slicer:
Go to Power Query Editor. Find CustomerID from fact table and check the column Null or empty value:
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:
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.
3. Click Done:
4. Click Close & Apply button, and you’re done!
If you have any questions about using Power BI, please feel free to connect with us.
Get the full report on the top BI Vendors.