Forum Discussion

nicolas_q's avatar
nicolas_q
Regular Visitor
4 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

    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. 

    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.

     

    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.

     

9 Replies

  • danextian's avatar
    danextian
    Super User
    4 years ago

    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

     

    • nicolas_q's avatar
      nicolas_q
      Regular Visitor
      4 years ago

      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 rebootedI would have 0 instead Blank

       

      Thank for you help

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.

        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. 

        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.

         

        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.

         

    • VizzCrafter's avatar
      VizzCrafter
      Frequent Visitor
      2 years ago

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

  • mahenkj2's avatar
    mahenkj2
    Solution Sage
    4 years ago

    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.