cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Daxel New Member
New Member

COUNT has an unexpected behavior with text string

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.

Capture1.PNG

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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: COUNT has an unexpected behavior with text string

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.

1 REPLY 1
Super User
Super User

Re: COUNT has an unexpected behavior with text string

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.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 79 members 1,534 guests
Please welcome our newest community members: