Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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)?
Solved! Go to Solution.
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 🙂
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.
If you want is to replace blank or null values with 0 without creating a new column, then use the Query Editor.
Notes:
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!!
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.
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?
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! 🙂
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
121 | |
112 | |
59 | |
58 |