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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
robs67stang
Frequent Visitor

Problems with using COUNTA with multiple choice cells from a Microsoft List

Good afternoon.  I am having some issues using COUNTA when counting populated column cells in data view that contains text from a Microsoft List that uses a choice selection (Yes, No, or left blank) in the column cells.  It appears that the COUNTA syntax is reading that the cells in the columns have text in them even thought they are blank.  I beleive it may be detecting that the column cells have a choice fuction in them from the Microsoft List, even when they do not have any text in them currently.  I can see the blank cells in the data view, but they are still being counted as populated in the report view.  The COUNTA syntax is working correctly for my column cells that use a date in them.  Below is the COUNTA syntax I am using.  Any insight on why this is occuring and how to work around this issue would be greatly appreciated.  Thank you for your help.

 

Complete = COUNTA('ALL'[AS9100]) + COUNTA('ALL'[FGOPS]) + COUNTA('ALL'[FOD Control Calculated]) + COUNTA('ALL'[Tool Control Calculated]) + COUNTA('ALL'[GSE]) + COUNTA('ALL'[Tow Operations]) + COUNTA('ALL'[Jacking Operations Calculated]) + COUNTA('ALL'[Mooring/Hangaring/Security]) + COUNTA('ALL'[Tire/Wheel]) + COUNTA('ALL'[Battery]) + COUNTA('ALL'[Corrosion]) + COUNTA('ALL'[HAZMAT]) + COUNTA('ALL'[Safe-for-Maintenance]) + COUNTA('ALL'[Egress Calculated]) + COUNTA('ALL'[W&B]) + COUNTA('ALL'[Technical Data (orders, manuals, IETMs)]) + COUNTA('ALL'[Aircraft Records Management]) + COUNTA('ALL'[CADS Calculated])

 

Best regards,

Brian

3 REPLIES 3
johnt75
Super User
Super User

From the Microsoft docs

Like the COUNTA function, the COUNTAX function counts cells containing any type of information, including other expressions. For example, if the column contains an expression that evaluates to an empty string, the COUNTAX function treats that result as non-blank. Usually the COUNTAX function does not count empty cells but in this case the cell contains a formula, so it is counted.

It could be that the cells contain some info rather than actually being blank.

It might be worth checking if the cells return true for ISBLANK().

Johnt75,

Thank you.  Using the ISBLANK() function verified that the cells are not truly blank when they appear to be. The results came back False for the ones that were in question.  Is there a function that would work to count only the cells that visually have content in them?  I have not been able to discover one as of yet.  Thank you for your help.

You could look into using Power Query to transform those cells into actual blanks. You could try using the trim function and then converting empty strings to null maybe.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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