In a previous post, What does this BLANK() mean?, we looked at why (BLANK) values sometimes appear in our visualizations. To summarize, there are two main causes for (BLANK) showing up on a chart or table visualization are:
- Source Data is Blank: There are blank, null or empty values (or cells) in your source data.
- Source Data is Missing Values: There is no record (or row) in the source data that relates to the specific information displayed in the visualization.
Note that Cause 1 can occur with a single table. Cause 2 can only occur when working with two or more tables, as the problem stems from the joining of tables. This join can be via relationships in the Data Model or merge in Power Query.
In the previous post, we focused on Cause 2 - Source Data is Missing Values and saw how this problem can occur when there is a record in the Fact table for a value that has no matching record in the Dimension table. In today's post, we are going to see what happens when the problem is reversed:
There is a record in the Dimension table but no matching record in the Fact table.
No Transaction Exists for that Dimension
In the image above, we are looking at three raw data tables: Sales, Customers and Calendar. They are related with a typical star schema, 1 to many relationship from the Dimension (Customers, Calendar) tables to the Fact (Sales) table.
If you examine these tables closely, you will notice that customers Suzy and Sam do not have any sales in the Sales table. In fact, I can see this by creating a simple column chart to show Qty by Customer Name:
Nothing seems (BLANK) or out of place in the visualization above, but a keen observer will notice that we have 8 customers with 8 distinct names in our Customers table, but only 6 columns in our visualization.
This scenario happens a lot more frequently than we realize, but the default setting is to 'hide items with no data' in our visualizations. In order to make it more obvious which customers have NOT ordered anything in the selected time frame, we have the option in Power BI to 'Show items with no data'.
When we activate the 'Show items with no data' our visualization now displays Sam and Suzy proudly and makes it very clear that they either Blank or Zero sales. We can't tell which when 'Show items with no data' is selected (though I can tell you that if you deselect 'Show items with no data' you will still see any Customers with ZERO value for sales, but any BLANK values will disappear.
To make this difference more obvious, we are going to change the column chart to a table, and I am also going to update the raw data so that Tom has a return on 10 July equal to his purchase amount on 5 July.
Now we can see the bottom left chart, Liters by Customer (Hide items with no data), has a blank or zero value for Tom. The bottom middle chart, Liters by Customer (Show items with no data), has blank or zero values for Tom, Sam and Suzy. The bottom right table shows clearly that Tom did indeed have orders, they just cancelled each other out to 0.00 liters. Sam and Suzy though have blank values in this table.
What does a (Blank) Measure Mean?
The blank value for Sam and Suzy indicates that neither of them had any sales. In fact, they have NO MATCHING TRANSACTIONS or records in the Sales (Fact) table. This actually tells us a lot about those customers, and can be a much more powerful insight than the 'unknown' values we looked at in our previous post on Identifying Blanks.
In this case, the (Blank) values indicate "No Sales".
If you find that the Blanks are confusing, you can create a custom measure to replace the Blank values with the more Descriptive "No Sales" value:
Total Qty Liters (Descriptive) =
VAR totalqty =
SUM ( Sales[Qty Liters] )
RETURN
IF ( ISBLANK ( totalqty ), "No sales", totalqty )
Note that while we still have the Total liters = 341.50 in the Descriptive column, the Total Qty Liters (Descriptive) is now a mixed data type measure. This may cause difficulties (or the need for extra conversions or data type detection) when trying more advanced DAX calculations using this measure. For now though, we can still see the Total value that we need, AND we have made it more clear what that empty value for Sam and Suzy actually means.
