Cannot see blanks in column chart

[Anonymous]

Hi All,

Any reason for this behavior?

No filters applied.

Ta!

[d_gosbell]

This is probably due to how you've set up "Count of PositionResponsible". 

If you are counting over the column in the related table with the invalid relationship that is responsible for generating the (blank) values then this is the expected behaviour as the related table would not include the explicit blanks. Or if you are doing COUNTROWS(DISTINCT()) the DISTINCT() function does not return blanks from invalid relationships.

[Anonymous]

Hi @d_gosbell 
Thanks for the answer.

The count is regular one, not distinct*. I just draged and droped the field to the Value box.

There is no relationships, all data originated from the same data source.

*Count Distinct shows the balnk value, but the numbers are wrong of course.

Ta!

[d_gosbell]

Ah, OK so those (blank) values must be explicit null values coming in from your data source. In that case the issue is that the default count that Power BI does uses the COUNTA function and from the documentation:

---

The COUNTA function counts the number of cells in a column that are not empty. It counts not just rows that contain numeric values, but also rows that contain nonblank values, including text, dates, and logical values.

---

So it is explicitly ignoring the blank values.

The fix for this is to create a new measure yourself that uses whatever logic you need to get your count. 

Maybe something simple like COUNTROWS( tablename )  will work.

[Anonymous]

Hey,

Thanks for the explanation.

But I find it a bit strange, as in the past I always saw blank values in my visualisations.

And yes, you are right. The blanks are original there, I just wanted to show them in the visuals I have created.

Any idea how this can be achieved?
Thanks

A

[d_gosbell · Accepted solution]

---

Any idea how this can be achieved?

---

Yes, like I said previously, you can create a measure and use something like COUNTROWS( yourtablename ).

Or if you do a sum or count over another column which does not have any null values it will work too. (which is why some previous charts may have worked)

Or you could replace the nulls with an explicit value like "UNKNOWN" or something like that as part of the Power Query definition when you import the data.

[Anonymous]

Hey @d_gosbell 

Thanks for the advice.

I decided to replace the null values on data load stage.

Thanks!
A