- Ciao friends.
Did it ever happen to you
to create a slicer by product color,
start using it and after a few days
a nasty blank row appears in the slicer?
You search in the product table
there are no rows anywhere without the color.
Still that row is there
and it's breaking the beauty of your report.
Now you could get rid of the blank
in that specific slicer by using a filter,
but you are creating more problems than you're solving.
Because if blank suddenly appears in your data,
that means that you have an invalid relationship.
And the blank row is a very important concept
that you need to understand well in DAX.
Because if a model contains a blank row,
then you need to pay attention to a lot of your calculation
to make sure that you are doing them right.
So in this video, I will walk through understanding
why the blank row might appear in the model,
how to get rid of it and what you need to pay attention to
whenever you have blank rows in your tables.
Let's get started.
In order to understand the concept well,
we will do several steps.
We start with a model that does not contain any blank row
and work just fine.
Then I will artificially create the blank row.
In the process I will explain you
the reason why the blank row appears.
After that we will see which functions use the blank row,
which function do not use them
and what are the dangers that you might have
because of the blank row.
So let's start with a model that work just fine.
I have a report that is slicing by color,
the sales amount and I have a slicer that contains category.
You see, there are no blanks anywhere.
Now focus on this value.
Black has 5,860
5 million, around $6 million of sales.
And the model is working just fine.
I'm slicing by color.
Now the color is one of the columns in the product table.
It's here, and when I slice by color
I can compute values from sales,
because the filter moves from product to sales
because of the relationship.
And this relationship right now is valid.
All the rows in sales point to the right product.
So everything is working just fine.
But what happens if I remove one of the colors?
Let's say for example, that I remove the black color
from the model, therefore, all the products
which are black suddenly disappear.
What is the engine expected to do at this point
about these $6 million?
But because there are no products,
if I slice by product, they might disappear.
But this is bad, because I will no longer
have the correct total.
So the engine is going to create a blank row,
a row that does not exist in the model, contains a blank,
that is related to all the products
that previously were in the model the black products
and they suddenly disappeared.
This row appears in the model
even though you cannot see it.
Let's do it together.
To artificially create the blank row
I need to go to transform data
and get rid of all the products which are BLACK.
So I go to the product table, select BLACK from somewhere,
search for it, here it is
just right-click Text Filters
and keep only the rows which are not black.
I do Close & Apply. This refreshes the model.
And now black is no longer here.
And you see what happened:
I have the blank row here in category that was
not present earlier, and I also have a blank here in product
that contains exactly the same amount of products
that were previously in the model.
I can go searching for this row.
I can go in the product table.
Let me search for it.
Let's go to color.
I can sort it ascending, look at the detail
but you see that the first color is Azure.
There are no rows anywhere that contain this blank.
Nevertheless, the blank row was inserted.
So right now my product table contains
all the products that have been loaded from the data model
plus an additional row containing a blank
which is used to justify, to relate
to all the rows that are now orphaned from their product.
And the value you see here is still correct,
the report still reports the correct total
because of the blank row.
So the blank row has a very good reason of being.
It exists in order to make a relationship
that is invalid, still work.
Nevertheless, you need to pay attention
to the presence of the blank row.
First of all, it breaks your slicer.
Now you have under category also the category blank.
Again, there are no products with a blank category,
except from the product containing the blank row
or for the blank row in the product table.
As I said this is an artificial row.
It does not exist in the model, but it is counted.
Some functions count it, some functions do not.
And we need to understand which function
consider the blank row as a valid row
and which functions do not.
For example, I might be interested
in counting the number of products.
And I can create a measure
that does just a COUNTROWS of product.
If I put this measure in the report, look what happens.
For every color I have the correct number of products,
except for the blank.
In the blank row I don't see any products
as if there were none.
And it's an interesting question.
Are there any products which are blank?
If you look at the data loaded from the database,
there are none, but if you consider the table
as it is after DAX processed it,
it actually contains that row.
If you want to see that product,
you cannot use just COUNTROWS of product.
You need to use COUNTROWS VALUES of product.
VALUES used with a table argument
does not return only the table,
but the table plus the additional blank row if it exists.
If I put COUNTROWS VALUES of product,
now you see that I see one in the blank.
The reason is, VALUES considers the blank row,
a table reference does not.
And this is not the only function
that has a difference in the handling of the blank row.
DISTINCT and VALUES, for example
they are two functions that do nearly the same thing.
They return the distinct values of a column,
but DISTINCT does not consider the blank row,
whereas VALUES does.
Let's see that with an example.
Let's get rid of number of products
and yeah, sales amount we can keep it.
Let's say that I create a new measure
that counts the number of colors
which are present in the model.
So I create a measure number of colors
which is COUNTROWS
of DISTINCT of product color.
I am using DISTINCT.
So the blank row will not be taken into account.
And if I put that in the report,
you see that it shows one everywhere and 15 at the total.
What if I want to see one also here?
Well, instead of using DISTINCT, I need to use VALUES.
Let's call this measure number of colors distinct,
and I can create a new measure.
I just copy the previous code.
This is number of colors with VALUES
that instead of using DISTINCT uses VALUES.
I put that in the report, now you see that I have one
and also the total is different.
Noting that the total is different is very important,
because depending on the function you use
you might have different results.
For example, you might be interested in computing
the percentage of products of every brand
compared with the total.
Let's do that.
Let's say that I want to compute the percentage of product,
the number of product divided by
the total number of product.
I can compute the percentage using VALUES
or using DISTINCT,
using DISTINCT.
And it's a division.
I divide COUNTROWS of product
by CALCULATE,
COUNTROWS of product.
I can actually make it easier by COUNTROWS ALL product.
This counts the number of products
in the current filter context.
This counts the number of all products and the division
one of the other produces a percentage.
I put that in the report,
drop it here and look at the result.
Let's see the formula too, percentage.
Yeah, this is just percentage without distinct.
Look at the total here, it's 99.95%.
Why is it not 100%?
The reason is, I do at the top,
I'm using COUNTROWS product.
COUNTROWS does not count the blank row.
At the denominator, I'm using COUNTROWS of ALL product.
ALL considers the blank row.
So the denominator contains not only the number of products
which I loaded from the database,
but also the one containing the blank row.
If I want to fix it
or if I want to obtain a good percentage,
I can do two things.
Either I count the blank row also add the numerator
or I do not count the blank row at the denominator.
So I can do COUNTROWS of VALUES of product.
If I use COUNTROWS of VALUES of product,
then also at the numerator, I will consider the blank row.
And if you focus on the total here,
you will see that as soon as I hit Enter,
this will go to 100%.
If I do not want to consider the blank row
neither at the numerator nor at the denominator,
then I should not use VALUES.
I should use DISTINCT product or just product,
because a table reference by itself
does not consider the blank row.
And instead of using ALL, I use ALLNOBLANKROW product.
Now, this does not consider the blank row
and this does not consider the blank row.
Again, the total will be 100%.
And you see that I still have 100%.
The presence of the blank row in your model
always comes from an invalid relationship.
If a relationship between two tables is not valid,
because some of the values in sales
reference products that do not exist,
then the engine creates the blank row.
And the presence of the blank row is important,
because all of your calculation might consider
or not the blank row.
We have seen DISTINCT and VALUES,
but there are a lot of other calculations
that might be broken, because in part of the calculation
you are considering the blank row
and in another part, you are not.
So the best thing that you can do is check the model
for invalid relationship.
If a blank row appears from nowhere,
that means you have an invalid relationship somewhere.
You need to search for it, fix it
and that is the best way of solving the issue.
If you cannot fix the problem for whatever reason,
then you will need first to understand
that the blank row exists for a reason.
If you hide it, you will hide the values
of all the invalid rows
producing totals which are not correct.
And if the blank row appears in your model,
pay attention to all of your calculation
and ask yourself if you're considering or not the blank row
in every step of your calculation.
Mixing part of calculation that does not use the blank row
with part that use it is likely to produce wrong results.
Enjoy DAX.