topic Re: CountA is counting blanks... ...and so is Countblank in DAX Commands and Tips

CountA is counting blanks... ...and so is Countblank

scottz — Wed, 16 Mar 2022 17:06:16 GMT

Basically the title. I have a very large table so it wouldn't be practical to post it here, but I am just doing a simple

Delivered Count = COUNTA('Weekly Reports'[Delivered])

and it's counting all the cells in the column. Okay-- probably means there some data there I don't see. Well fine then, I change the formula to:

Delivered Count = COUNTBLANK('Weekly Reports'[Delivered])

and it counts the blanks perfectly.

What in the world is going on? How can one formula fail and the other work?

Re: CountA is counting blanks... ...and so is Countblank

amitchandak — Wed, 16 Mar 2022 17:11:51 GMT

scottz , COUNTA - The COUNTA function counts the number of cells in a column that are not empty

so that will leave blank

https://docs.microsoft.com/en-us/dax/counta-function-dax

Try countrows('Weekly Reports')

Re: CountA is counting blanks... ...and so is Countblank

scottz — Wed, 16 Mar 2022 17:27:40 GMT

So, I think I follow now. Empty=null, Empty =/= blank. So CountA needs a null, not a blank for the function to correctly not count that cell. So if I did 'replace values' in powerquery and changed <blank> to null that solves my problem?

Re: CountA is counting blanks... ...and so is Countblank

amitchandak — Thu, 17 Mar 2022 02:48:44 GMT

scottz , in power query replace null with say NA

null should be all small caps

Re: CountA is counting blanks... ...and so is Countblank

V-lianl-msft — Mon, 21 Mar 2022 07:02:04 GMT

Hi scottz ,

Yes, please replace the blank ("" or " ")with null.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.