Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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


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.

View solution in original post

6 REPLIES 6
d_gosbell
Super User
Super User

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
Not applicable

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!

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
Not applicable

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


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
Not applicable

Hey @d_gosbell 

Thanks for the advice.

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

Thanks!
A

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.