Forum Discussion

Vickram's avatar
Vickram
Helper III
5 years ago
Solved

Handling NULL values

I have seen many posts and solution re handling NULL values in Power BI but I could not find the solution I am seeking. 

I have a column with variant data type: Number and NULL. So, this data cannot dispalyed in bar chart or column chart etc. So, I replaced NULL with 0, now all visualization works perfectly fine.

My next requirement is to check if the values are NULL then exclude it from the IF condition. I cannot use the previously replaced 0 values in my IF condition because some of the values for this column is genuinely 0. So, if I exclude all 0s, then the actual column value of 0 will also be excluded in the IF condition, this is not what I want.

Any solution to this? If this type of issue has already been discussed and resolved please point me to that direction.

Thanks.

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    The Data type of the column should be decimal and you should not feel the need to replace null with 0.  Your visuals should work perfectly.  Can you share the file's download link and show the exact problem.

6 Replies

  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion
    5 years ago

    Keep your null values as is in the table and you can create a measure like this to handle NULL Values

     

    _Test = CALCULATE(IF(SUM('Table'[Column2])=BLANK(),0,SUM('Table'[Column2])))

     

    • Vickram's avatar
      Vickram
      Helper III
      5 years ago

      It did not work. Throws below error when tried to display the measure in a key card.

       

      • Vickram's avatar
        Vickram
        Helper III
        5 years ago

        Also, checking null values in IF condtion as below does not work

        e.g. =IF([colA]=null, 0, [colA]). Can we check null using IS NULL like =IF([colA] IS NULL, 0, [colA]) just like in SQL.

        Not sure how to check if the value is NULL in Power BI:

        [colA]=null

        or

        [colA] is null

        or

        [colA] ="NULL"

        or

        [colA]="null"

        or

        [colA]=BLANK()

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    The Data type of the column should be decimal and you should not feel the need to replace null with 0.  Your visuals should work perfectly.  Can you share the file's download link and show the exact problem.

    • Vickram's avatar
      Vickram
      Helper III
      5 years ago

      Thanks heaps Ashish. When data type converted to Decimal, yes the visualization works fine even with the NULL values 

      Little bit curious about below:

      • The column has NULL values in SQL server database which I extracted to Excel table (as the machine I am working on does not have SQL Server DB connection). So, this Excel table is the source of my power BI.
      • When data is exported from the Excel, Power Query Editor shows the null values as NULL
      • When data type is converted to decimal, the null values becomes null in Power Query Editor window
      • When "Close and Apply", the table in DAX or main PowerBI windows, shows the null values as blank
      • The same column also contains values which are 0s (zero). So,the column now contaions number values, 0 and blank values.
      • Now, while using that column in IF condition, 0s are also treated as being blank

      This is bit strange to me. Anyway, I found a way to deal with 0 being treated as blank by using ISBLANK() function. So, all good. If you can shed some light why Power BI treats NULL as explained above, would be great.

      Thanks again Ashish.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        You are welcome.  In the Query Editor, Null is treated as blank.