Forum Discussion

Espen1515's avatar
Espen1515
Advocate 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 e...
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 :)

Espen1515's avatar
Espen1515
Advocate 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
Advocate 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 :)

  • Espen1515's avatar
    Espen1515
    Advocate I
    8 years ago

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

    • Pavlous's avatar
      Pavlous
      Advocate II
      8 years ago

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

      • Espen1515's avatar
        Espen1515
        Advocate I
        8 years ago

        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.

  • deSanta's avatar
    deSanta
    Regular Visitor
    4 years ago

    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.