Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
27 | |
10 | |
10 | |
9 | |
6 |