Replace (blank) with 0 in Power BI

Replace (blank) with 0 in Power BI

I recently did a bit of work in Power BI and came across this issue :

An unsightly (Blank) being displayed whenever a numeric value is zero.

What you really want of course is to see a 0 when it is zero. I put up with it for a while but eventually had to look for a solution.

I experienced this in particular when narrowing a date range filter, so I have used that as an example here.

power bi 1

How to fix the (Blank)? Use a measure.

My table name in this example is called ‘Quick Books’.

Add a new measure (Power Query) to the table and include the field name that has the (Blank) value – in this case ‘Credit’.

powerbi 2
Add a new measure (Power Query) to the table and include the field name that has the (Blank) value – in this case ‘Credit’.

The Power Query code I use

Measure name = IF(
ISBLANK(COUNT('Quick Books'[Credit])),
0,
COUNT('Quick Books'[Credit]))

Create a new measure in your table via the ‘New Measure’ button.

This by default calls the measure name ‘Measure Name’ and it shows up as a field in your field list on the right-hand side.

powerbi 3
The measure shows up as a field in the field list

And of course change to use your own field names

Replace ‘Measure name’ with your own name.

Replace ‘Quick Books’ with your table name.

Replace ‘Credit’ with your field name.

Save/Commit the measure

Click on the tick next to the measure to commit.

You can always return to edit it by clicking on the field/measure name in the field list.

powerbi 4
When finished click on the tick to commit/save measure

Replace original field with the measure

powerbi 5
Replace the original field name called ‘Credit’ with ‘Measure name’

To actually get it to use the new measure select the ‘Numeric Value’ visual and replace the original field name (Credit) with the measure (Measure name)

powerbi 6
The ‘Credit’ field has been replaced with ‘Measure name’, instead of (Blank) we now see a better looking zero.

As expected when we increase the date range the correct values are still displayed:

powerbi 7

Leave a Reply

Your email address will not be published.