DEV Community

loading...
Cover image for Replace (Blank) with 0 in Power BI

Replace (Blank) with 0 in Power BI

graemetg profile image Graeme T-Gill ・2 min read

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.

Alt Text

Use a measure to replace the (Blank) with 0

Add a new measure (Power Query) to the table via the ‘New Measure’ button.

Alt Text

Include the field name that has the (Blank) value — in this case it is ‘Credit’.

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

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

Alt Text

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.

Alt Text

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.

Alt Text

Replace original field with the measure

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).

Alt Text

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

Alt Text

From (Blank) to 0 to hero!

Discussion (0)

pic
Editor guide