topic Re: Count text values and return 0 if blank in DAX Commands and Tips

Count text values and return 0 if blank

maneschr2022 — Mon, 25 Apr 2022 23:38:24 GMT

Hi everyone!

I´m looking for a DAX that allows me to count the total number of a specific text value in a column, but if it is none/blank I want to show 0 instead of blank.

I was trying to use this DAX formula:

Count of Red colour= COUNTROWS(FILTER(TABLE, TABLE[Colour]="Red")) +0

But for some reason when I add this measure as a value in a table it doesn´t load (my table already has a lot of values, not sure if that is related to that issue) when I remove the +0 it loads alright in the table, but I would like to show the 0 if there blank.

Is there another DAX that is better to do this? thanks!

Re: Count text values and return 0 if blank

tamerj1 — Tue, 26 Apr 2022 00:52:59 GMT

Hi maneschr2022

use

Count of Red colour= IF ( ISBLANK (COUNTROWS(FILTER(TABLE, TABLE[Colour]="Red"))), 0, COUNTROWS(FILTER(TABLE, TABLE[Colour]="Red")))

Re: Count text values and return 0 if blank

Anonymous — Tue, 26 Apr 2022 06:49:27 GMT

Hi maneschr2022 ,
this seems a very apt use case for the COALESCE() function. You can use it like :

Count of Red colour NOBLANK = COALESCE(COUNTROWS(FILTER(TABLE, TABLE[Colour]="Red")),0)

Regards

Re: Count text values and return 0 if blank

maneschr2022 — Thu, 28 Apr 2022 01:40:05 GMT

Hi tamerj1 and Anonymous both Dax work in terms of getting 0 instead of blank on my table visual, the only problem is that I get multiple rows instead of concentrated in one row, I`m using a column ID that generates the rows, but with this formula, I'm getting multiple times the same ID for 0, is there any DAX that can cover that issue as well all together?.

For the creation of my table, I'm using columns that belong to 2 different tables but they share in common the ID column.

Re: Count text values and return 0 if blank

tamerj1 — Thu, 28 Apr 2022 01:57:55 GMT

maneschr2022

Please try

Count of Red colour = IF ( NOT ISBLANK ( COUNTROWS ( TABLE ) ), IF ( ISBLANK ( COUNTROWS ( FILTER ( TABLE, TABLE[Colour] = "Red" ) ) ), 0, COUNTROWS ( FILTER ( TABLE, TABLE[Colour] = "Red" ) ) ) )

Re: Count text values and return 0 if blank

maneschr2022 — Thu, 28 Apr 2022 03:29:51 GMT

Thanks a lot! works perfect now!

Re: Count text values and return 0 if blank

zendu — Wed, 26 Jul 2023 01:11:43 GMT

This approach of using Coalesce() is much cleaner and easier to maintain as calculation is not repeated.