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
chandakaushik
Helper I
Helper I

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

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

10 REPLIES 10
parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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

@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

 





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

Proud to be a Super User!




 , @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.

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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?

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @chandakaushik , 

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





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

Proud to be a Super User!




So... personally I usually pull data from external data sources.  This is one of those areas that is kind of frustrating, as PowerBI seems to read those empty cells and places blanks instead of null values.  The only way I know of to fix this is to replace <nothing> with null on the column to correct the default behaviour.

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.