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

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.

  • Pavlous's avatar
    Pavlous
    Advocate II
    8 years ago

    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.

    • Espen1515's avatar
      Espen1515
      Advocate I
      8 years ago

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