cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chandakaushik Frequent Visitor
Frequent Visitor

COUNTA function is counting blank cells as well

Did anyone notice that COUNTA DAX function is counting blank cells in the column as well? Microsoft's documentation says it counts the number of cells in a column that are not empty. Any idea what's happening? The column I am counting has text data type.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: COUNTA function is counting blank cells as well

that's what is looks like, and only reason you need to do that because you are using COUNTA or similar function which doesn't count BLANK otherwise you can always put condition in your measure to check for whitespace and blank() but replace can be more elegant solution.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

10 REPLIES 10
Super User
Super User

Re: COUNTA function is counting blank cells as well

@chandakaushik can you share sample dataset, what you are trying to do and what is not working? If it actually BLANK() or empty value in text field.

 

here is post on more detail on BLANK






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





chandakaushik Frequent Visitor
Frequent Visitor

Re: COUNTA function is counting blank cells as well

@parry2k I entered the data directly in PBI Desktop using "Enter Data" . Here is the sample dataset.

 

When using COUNTA on address column, it gives me a count of 10 while there are only 6 values in the column. I tried using ISBLANK function on address column, it gives me FALSE for each value. 

 

COUNTA.png

 

COUNTA2.png

Nathaniel_C Super Contributor
Super Contributor

Re: COUNTA function is counting blank cells as well

@chandakaushik @parry2k ,

 

That is interesting. I am getting the same results, however if I load a table from Excel, the counta function works as expected, or rather Excel does not load anything into a blank cell.

 

Nathaniel

 

Super User
Super User

Re: COUNTA function is counting blank cells as well

Seems like when you are entereing data manually, it is not blank but white space. why not open you table in query editor and check if you see null or whitespace






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





chandakaushik Frequent Visitor
Frequent Visitor

Re: COUNTA function is counting blank cells as well

 , @Nathaniel_C  - Yes, you're right Nathaniel. This is surprizing me as well. I don't understand how PBI is rendering blank cells in manually created tables.

@parry2k  - I entered the data manually and I'm sure there are no white spaces in the table. It's not showing null for blank cells in Power Query. When I use ISBLANK(TRIM([Address])), it still returns FALSE.

Super User
Super User

Re: COUNTA function is counting blank cells as well

@chandakaushik based on your screen shot, it is not blank it has whitespace and that's why counta is not working

 

here I entered data manually, and how the table looks like and in 2nd image below, I used the replace command to replace whitespace with null and tha'ts when I see null and in this case it will work with BLANK() and 2nd case your COUNTA function will work

 

image.pngimage.png

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





chandakaushik Frequent Visitor
Frequent Visitor

Re: COUNTA function is counting blank cells as well

Does that mean if I enter data manually in PBI Desktop, blank cells are rendered as white spaces, which I will have to replace to null?

Super User
Super User

Re: COUNTA function is counting blank cells as well

that's what is looks like, and only reason you need to do that because you are using COUNTA or similar function which doesn't count BLANK otherwise you can always put condition in your measure to check for whitespace and blank() but replace can be more elegant solution.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Nathaniel_C Super Contributor
Super Contributor

Re: COUNTA function is counting blank cells as well

Hi @chandakaushik , 

You may want to ask @KenPuls . He wrote an amazing book about Power Query.  M Is for (Data) Monkey.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 312 members 2,909 guests
Please welcome our newest community members: