If you use a field in a visual and based on a combination of slicer selection, that field doesn’t have any value, you might see blank in the visual. If you want to replace that blank to zero, here is a trick for you. a very simple trick that you can use anywhere easily.
I have a sample report here that shows the SalesAmount (which is a field in the FactInternetSales table of the AdventureWorksDW excel file) as a card visual, which a slicer for Calendar Year (DimDate);
The problem is that if I select a year such as 2010, I get the SalesAmount as Blank:
So, what is the solution? really simple. using a DAX measure. let’s see.
Creating a DAX Measure
One easy way to replace the (Blank) with zero or anything else, is to use a measure. You can create a measure in the Modeling tab;
Then write the DAX expression of the measure like this:
Sum of SalesAmount = SUM(FactInternetSales[SalesAmount])
So far, this should give you simply the same output if you use it in the visual;
Now you can add a conditional statement like this:
Sum of SalesAmount - zero instead of blank = var _sales=SUM(FactInternetSales[SalesAmount]) return IF(ISBLANK(_sales),0,_sales)
The expression above might looks scary if it is the first time you are writing a DAX statement. However, when you look at it in detail, It is using a variable for the sum of the SalesAmount field, then checks to see if the result is blank or not, if blank, it returns zero, otherwise the sum of SalesAmount. Using the variable makes your code easier to read and faster. however, it can be done also without a variable like this:
Sum of SalesAmount - zero instead of blank - no variable = IF( ISBLANK(SUM(FactInternetSales[SalesAmount])), 0, SUM(FactInternetSales[SalesAmount]))
Now, if I use that measure in the card visual, I get zero instead of blank.
There are a few points to consider when you are doing this change:
Make sure you format the measure correctly in the Measure tools tab
depends on the data type and desired format, select the right formatting in the measure tools tab.
Aggregation is not always SUM
Depends on what is the default aggregation of the field you have used in the visual, you have to use the same aggregation in the measure. If aggregation is Average, then use the Average Function and so on.
This method does not replace the blank in the data row
This method just presents the blank as zero in a visual. Behind the scene in your data rows, data is still in the same shape as it was before.
Hide the Original Field
After creating the measure of your data field, then you better to hide the original field, because you won’t use it any more directly. the measure is what you can use from now on.
Replace Blank with Other Values
If you want to show something else instead of blank (not zero), you can use that in your expression, such as below;
Be careful of using this method in other visuals
This method works perfectly for some visuals which shows only one value, such as Card, Gauge, KPI visual, etc. However, if you use it in a visual with an axis, then you might get undesired results.
here you can see an example:
Returning a value instead of blank means that in a visual with an axis (table, matrix, column/bar charts, etc), if for a combination, there is no data, still the visual will show that with zero as a result.
Download Sample Power BI File
Download the sample Power BI report here: