Forum Discussion

Espen1515's avatar
Espen1515
Icon for Advocate I rankAdvocate I
8 years ago
Solved

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

 

Need Help
  • Pavlous's avatar
    Pavlous
    8 years ago

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

16 Replies

  • jthomson's avatar
    jthomson
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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

    • mycauldron's avatar
      mycauldron
      New Member
      3 years ago

      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

    • mohammadyousaf's avatar
      mohammadyousaf
      Icon for Resolver II rankResolver II
      5 years ago

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

    • Espen1515's avatar
      Espen1515
      Icon for Advocate I rankAdvocate I
      8 years ago

      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?

      • Pavlous's avatar
        Pavlous
        Icon for Advocate II rankAdvocate II
        8 years ago

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

  • Pavlous's avatar
    Pavlous
    Icon for Advocate II rankAdvocate II
    8 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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!! 

  • rodlovely's avatar
    rodlovely
    Regular Visitor
    6 years ago

    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
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.