Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Espen1515
Advocate I
Advocate I

Replacing (Blanks) with 0

Hi,

 

I have a number of Cards in my dashboard that return numbers. When I selected a filter where there no numbers, instead of displaying 0, it displays (Blank), and looks very ugly. Is there an easy way of getting these to simply display the number 0 or "" instead of (Blank)?

 

Blanks.png

1 ACCEPTED SOLUTION

@Espen1515,

so you are using the raw source data, is it correct?

 

So its easy to you just make new column with this code

 

Column = IF(ISBLANK(**original column**),0,**original column**)

**original column** - your name for the column you are using.

Then change the value to the new column and it should work 🙂

View solution in original post

16 REPLIES 16
Anonymous
Not applicable

Hello Everyone,

I have a similar problem. I have blank values on my column (Interger Data Type) and for my visualization I need the blank values. On my power BI desktop it works very well. But when I publish the dashboard on the portal power bi services (premium), and my blank values are converted automatically into 0 and my curves changed and are "wrong". Why don't we have the similar behaviour between power BI desktop and power bi services ? Is there any parameters to change? I tried some dax queries but nothing works. I use Direct Query ...

Thank you for your help.

rodlovely
Regular Visitor

If you want is to replace blank or null values with 0 without creating a new column, then use the Query Editor.

  1. Select Edit Query. 
  2. In the query window, right click on the column header and select Replace Values... 

Notes:

  • Pay attention to where you insert the command in your Query steps. 
  • You can select multiple columns by holding down the Ctrl key when you select the column header
  • You can view and modify the code in the Advanced Editor if needed.
  • You can replace any values with this method
Su_Prasad
Regular Visitor

There is a simpler solution, 

Click on "Edit Queries" ribbon in the toolbar
Select the "Database"(Incase if you have multiple database imported into PBi )
Select the respective "Column" in that databse which has blanks
Choose the option "Replace Value"
Value to find: null
Replace with: 0
Click ok

 

Taaadaaa!! 

Pavlous
Advocate II
Advocate II

Hello @Espen1515,

There is quick fix. Lets say its measure [XAZ]. The Easiest way to do it is to make new measure

XAZ1 = IF(ISBLANK([XAZ]),0,[XAZ])

Same if its based on column, just do new column insted of measure 🙂 Or implement the condition inside original measure.

jthomson
Solution Sage
Solution Sage

Adding +0 to the end of your measure is one quick way to fix this, or adding a new measure that looks at the existing one, and does a simple "if blank then 0 else existing measure" replacement

Simple, as jthomson states.  Create your Measure then add +0

Below is a measure to count the number of 2008 OS as it came up (Blank) made my report ugley. 

 

Count of OS for Windows Server 2008 =

CALCULATE(

    COUNTA('Company'[OS]),

    'Company'[OS]

        = "Windows Server 2008"

) +0

Great quick Solution 🙂

Thank you, I just added the 0 after ) and it worked for me. simplest solution ever. 

Hi, I am simply using a card where I COUNT and filter on a particular field. I don't think I am using a measure. When you say measure here, do you mean that I should create a new column that copies the column, but using the if function instead?

@Espen1515,

so you are using the raw source data, is it correct?

 

So its easy to you just make new column with this code

 

Column = IF(ISBLANK(**original column**),0,**original column**)

**original column** - your name for the column you are using.

Then change the value to the new column and it should work 🙂

Hi, I am very new to Power Bi, and I have a similar problem. However I have multiple columns on my data field, and I want to put zeros on the empty spaces. Is there a way to set a singe rule that runs on the entire field?

 

Thanks on advance. 

Hi,

 

Yes, that is correct. I will try that. I was just hoping that I would not have to create a whole new column and instead just select a format or something like that on my Card. Something like "display blanks as "-" or display blanks as 0, etc. Power BI should have a function like that in order to minimize the amount of tampering with source data. 🙂

Hi,

 

You dont have to worry about "amount of tampering". When you are creating custom columns it doesnt take any memory. Its one of the main benefits using DAX instead of Excel functions 🙂

Hi,

 

I tried using your function now, but as this is a date field, it is returning numerical values for all dates where it is not blank. If I then convert those to date format, my 0's show as 30th December 1899 instead.

Hi @Espen1515,

thats changing the situation a bit.

You can try work-arounds.

 

1st of all its needed to know, that powerBi supports only one format inside one column. Which means that 0 in DateFormat is always shown as the date you wrote.

Only blank value ("null") is in neutral format, which shows "blank".

 

So if you want to show Date or 0, you would have to make a column in text format. Which will be limited in some functions.

Or you can try establish a visual / page filter where your original column will be set to all non blanks and it will be the value of data cards.

Hi,

 

Thanks. Ya I thought it was something like that. Anyhow, I think Power BI ought to have a functionality (similar to conditional formatting), where you can select any graph, table, card, KPI, etc., and say if value = blank, then X, etc.

 

Anyhow, I think I have the best solution possible. So thanks for your help! 🙂

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.