A common problem I see some people struggle with in Power BI is when a slicer contains a (Blank) record for some reason. The cause of the problem is not obvious and hence it is not clear how to fix it. This article describes what causes this to occur and also how to fix it (properly, and also with a work around if you can’t fix it properly).
The Problem
There are 2 types of tables in Power BI – Data (transaction) tables and Lookup tables. A properly designed Power BI data model will always ensure that the lookup table contains ALL of the unique items in the data table – nothing is missing. The problem of a (Blank) in a slicer occurs when you have an entry in the data table for which there is no corresponding entry in the lookup table. When this happens, you will end up with a (Blank) in a Slicer attached to the lookup table as well as in the Visual.
Example
Let’s say you have a Products table (Lookup table) and a Sales table (Data table) and you have sales recorded in the Sales table for a product that is not present in the Products table. In this example, when you add any column from the lookup table (eg Product Name) to a Slicer and/or a Visual in Power BI, you will see a blank value for the missing product as shown in the image below.
The Solution
You can (and should) fix the problem at the data source. This is the best solution – fix it at the source. But in some cases it may not be viable to fix it at the source. If this is the case, you can resolve the problem using Power Query. You simply retrieve the missing entries in the Lookup table from the Data table and append them to the Lookup table. In the example below, I have given the missing product a name “Unknown”. After making this change, the Slicer and the Visual reflect that it is an “Unknown” product instead of returning a blank, as shown in the image below.
The Method
Rather than write step by step instructions on how to do this in Power Query, I have recorded a video and posted it to my YouTube Channel.
And, here is the link to the Power BI Report Themes blog article that I mentioned in the video.
I have a table of courses, and another of mandatory courses. The ‘mandatory’ courses table filters the ‘courses’ table. I want users to be able to filter to courses that ARE NOT mandatory, so adding a slicer to my report which filters to ‘Blank’ values works a charm – that’s what I want to happen, and is expected behaviour, nothing wrong with my data.
What I don’t want to happen is for the slicer to display ‘Blank’, but instead ‘Optional courses’ and ‘Mandatory courses’.
Yes, I could do a merge and add a conditional column in Power Query; but surely there’s something simpler available (perhaps a DAX column or measure)?
As a general rule, a lookup table should have a complete set of values in a data table. In your case, it sounds like the mandatory courses table is a subset of the full course table. This is why the blank is generated. It’s not broken, but it’s not the way I would model the data. I would, in power query
1. start with the full list of courses
2. merge the mandatory table with the table above, and expand at least one column so you can see the matches
3. write a conditional column that returns “Mandatory” or “Optional” as a new column in Power Query
4. Only load the full table of courses, not the mandatory table
Good sharing Matt.
Alternatively one can import ‘HierarchySlicer’ visualization, it also work nicely.
Go to Format, at Selection control just type the name at the ‘Empty leaf label’ i.e. ‘Unknown’ (using Matt example above). Problem solve. Thanks
Regards, Yasir
What happens on the next update when product T appears?
Hi,
Is it possible to solve the issue of blank values in slicer using DAX functions instead of creating staging tables as shown in the video.
Thank you.
A blank in a slicer is there for a reason – it is because there is missing data. You should fix the missing data. You could filter out the blanks by going to the filter section for the slicer and setting the filter to exclude blanks, but I don’t recommend that – you should fix the problem.
Alternatively one can import ‘HierarchySlicer’ visualization, it also work nicely.
Go to Format, at Selection control just type the name at the ‘Empty leaf label’ i.e. ‘Unknown’ (using Matt example above). Problem solve. Thanks
Regards, Yasir
Pingback: Pre-Filtering Power BI Slicers – Curated SQL
Hi Matt, if some of your viewers would want to investigate which table relationships suffer from the blank row phenomenon the following queries may help to clarify this. The queries should be run in DAX studio or any other tool that can execute a DAX query.
// detects blank row condition – returns true/false
EVALUATE
ROW (
“Blank row exists between 2 related tables”, NOT (
ISEMPTY (
EXCEPT (
DISTINCT ( manytable[onetableforeignKEYcolumn] ),
DISTINCT ( onetable[primaryKEYcolumn] )
)
)
)
)
// returns the foreign keys from the many-table that are missing in the one-table or an empty table if none are missing.
EVALUATE
EXCEPT (
DISTINCT ( manytable[onetableforeignKEYcolumn] ),
DISTINCT ( onetable[primaryKEYcolumn] )
)
Example:
********
If a sales table ‘Sales’ exists and the sales table is related to a product table ‘Products’ and they are related through the ‘Sales'[Productkey] and ‘Product'[Productkey] columns then …
EVALUATE
ROW (
“Blank row exists between 2 related tables”,
NOT (
ISEMPTY (
EXCEPT (
DISTINCT ( ‘Sales'[Productkey] ),
DISTINCT ( ‘Product'[Productkey] )
)
)
)
)
… will return TRUE if a blank row exists otherwise FALSE.
This query:
EVALUATE
EXCEPT (
DISTINCT ( ‘Sales'[Productkey] ),
DISTINCT ( ‘Product'[Productkey])
)
Will return the product keys that don’t exist in the ‘Products’ table or an empty table (no rows) if all product keys from the sales table are contained in the ‘product’ table.
Hope it can help somebody to preflight the data model and make the adjustments needed (as you show in the video).
Best regards Jes.
Pingback: Power BI visuals, Q&A updates, a Cold Open and more... (December 10, 2018) | Guy in a Cube