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

Get 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

Reply
nicolas_q
Regular Visitor

Replace Blank with 0

Hello all,

 

the subject has been already discussed but I can't find a way to achieve what I need.

The use case is the following:

For some computers if a reboot is needed I have a value "1" if the reboot is not required then it's "0".

 

When I use a card visual, I want to display all the computers which need to reboot, I can filter the computers where reboot is needed is equal "1". It works well

But if all my computers have already rebooted, means all of them have a value equal "0", in the visual I have "Blank". instead of "0".

I can't find a way to replace the blank value by "0".

 

Do you have an idea?

Any help would be appreciated, Thanks

 

Nico

1 ACCEPTED SOLUTION

Hi @nicolas_q ,

 

I can reproduce your problem. I think this should be caused by your data model. You table should look like as below.

RicoZhou_0-1653377155093.png

And you use count function in value field in card visual and filter [Reboot] column to get result. When you show items when the value is not 0 in [Reboot], Power BI couldn't find any hostname and it will return blank. Blank in Power BI logic is same as 0. 

RicoZhou_1-1653377231455.png

If you don't want to show blank in card visual, please try to create measure.

Count hostname = 
CALCULATE(COUNT('Table'[hostname]))+0

Result is as below.

RicoZhou_2-1653377438415.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

9 REPLIES 9
mahenkj2
Solution Sage
Solution Sage

Hi @nicolas_q ,

 

When you get data from your sql source, what is data type?

How that data look, like 1 is shown as 1 and 0 is shown as 0 or blank/null?

 

If it is blank/null and data type is number, then first convert data type to text. Because replace function in power query can only replace null or blank with something if it is text.

 

Then replace null or blank with 0.

 

Then you can convert back the column to whole number if needed.

danextian
Super User
Super User

Hi @nicolas_q ,

 

You can modify your measure to include + 0.

MyMeasure = 
[My Original Measure or Aggreation) + 0
//-0 will also work, 0 + will also work

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Thanks for sharing this simple and easy solution. Thank you!

Hi, thanks for you reply,

Well i'm really new into BI, I'm sorry but I don't understand.

I collect the data from SQL database, it arrives into a column with a 0 or 1 values.

In the visual, I get the column but I didn't not create a measure.

I don't know what kind of measure i can add?

 

Here what I have:

what I have if all my computer are rebootedwhat I have if all my computer are rebootedI would have 0 instead BlankI would have 0 instead Blank

 

Thank for you help

Hi @nicolas_q ,

 

I can reproduce your problem. I think this should be caused by your data model. You table should look like as below.

RicoZhou_0-1653377155093.png

And you use count function in value field in card visual and filter [Reboot] column to get result. When you show items when the value is not 0 in [Reboot], Power BI couldn't find any hostname and it will return blank. Blank in Power BI logic is same as 0. 

RicoZhou_1-1653377231455.png

If you don't want to show blank in card visual, please try to create measure.

Count hostname = 
CALCULATE(COUNT('Table'[hostname]))+0

Result is as below.

RicoZhou_2-1653377438415.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Had this exact problem and found this thread. Your suggestion to create the "+0" measure worked perfectly. Thanks for the advice!

Hello, 

thanks for your help, i've tried several things, and i could find this solution :

CUSTOM_REBOOT_BLANCK/0 = IF(ISBLANK(Sheet1[reboot]),0,Sheet1[reboot])
 
Thanks for your help 🙂

Hi @nicolas_q ,

If I misunderstood in my first reply and you are just interested to hide blank of card visual (but it will not still show as 0, will just hide text (Blank):

Pls refer below link:

https://community.powerbi.com/t5/Desktop/How-to-deal-with-a-quot-BLANK-quot-value-in-a-card-visualiz...

 

Hi @nicolas_q,

 

The kind of aggregation in a measure will actually vary and they may or may not yield the same result.

Measure1 =
CALCULATE ( COUNTROWS ( Sheet1 ), FILTER ( Sheet1, Sheet1[hostname] <> 0 ) ) + 0
//this seems to be the closest to your use case
Measure2 =
CALCULATE ( SUM ( Sheet1[hostname] ) ) + 0
//assuming that summing up hostname is equivalent to counting each row in it that has a value of 1

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.