COUNT has an unexpected behavior with text string

[Daxel]

I would expect COUNT and COUNTBLANK  to calculate the complementary rows with in a column: non-blanks values versus blank values.

Therefore, COUNTROWS = COUNT + COUNTBLANK.

Expected behavior:

Both null value and empty text string value should be considered a Blank value by both functions.

Observed behavior:

COUNTBLANK considers both null and empty value as blanks.

COUNT considers only null value as blanks.

The following Table with a text field has an empty string value and a NULL value.

Total Rows = COUNTROWS('Table') = 4

Blanks = COUNTBLANK('Table'[Text]) = 2

Non-Blanks = COUNT('Table'[Text]) = 3

Is this a known issue with COUNT (and COUNTA) functions?

Or can you explain why both functions do not manage empty text value the same way?

Thanks

Julie

[d_gosbell · Accepted solution]

I believe that COUNT/COUNTA are working correctly and that the inconsistency is in the fact that COUNTBLANK is treating empty strings as blanks.

If you check this function in DAX Guide you will see a note that COUNTBLANK has this behaviour even though ISBLANK returns false for empty strings https://dax.guide/countblank/

Often issues like this can be traced back to the legacy of DAX having first been released in the PowerPivot addin for Excel. As such a great deal of emphasis was placed on making sure that DAX was consistent with Excel. If you check here there is also an Excel function called COUNTBLANK https://support.office.com/en-gb/article/countblank-function-6a92d772-675c-4bee-b346-24af6bd3ac22 which also counts empty strings as blank. 

So this is not a bug or an error, but a design compromise that was made in order to be more consistent with Excel.

In hindsight I'm sure that there are a number of design decisions that the product team made in order to maintain consistency with Excel that they would love to change, but we are pretty much stuck with them now as there is a huge installed base relying on the current behaviours.