Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
Thanks heaps Ashish. When data type converted to Decimal, yes the visualization works fine even with the NULL values
Little bit curious about below:
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.
You are welcome. In the Query Editor, Null is treated as blank.
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])))
Proud to be a Super User!
It did not work. Throws below error when tried to display the measure in a key card.
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()
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |