cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AClerk Senior Member
Senior Member

Cannot see blanks in column chart

Hi All,

Any reason for this behavior?

No filters applied.

Ta!

2019-04-09 11_53_09-papercut - Remote Desktop Connection.png

1 ACCEPTED SOLUTION

Accepted Solutions
d_gosbell Senior Member
Senior Member

Re: Cannot see blanks in column chart


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.

6 REPLIES 6
d_gosbell Senior Member
Senior Member

Re: Cannot see blanks in column chart

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.

AClerk Senior Member
Senior Member

Re: Cannot see blanks in column chart

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.

2019-04-09 13_46_11-papercut - Remote Desktop Connection.png

Ta!

d_gosbell Senior Member
Senior Member

Re: Cannot see blanks in column chart

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.

AClerk Senior Member
Senior Member

Re: Cannot see blanks in column chart

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 Senior Member
Senior Member

Re: Cannot see blanks in column chart


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.

Highlighted
AClerk Senior Member
Senior Member

Re: Cannot see blanks in column chart

Hey @d_gosbell 

Thanks for the advice.

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

Thanks!
A