Count text values and return 0 if blank

[maneschr2022]

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!

[maneschr2022]

Hi @tamerj1 and @Aditya_Meshram 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.

[tamerj1 · Accepted solution]

@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" ) )
    )
)

[maneschr2022]

Thanks a lot! works perfect now!

[Aditya_Meshram]

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

[zendu]

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

[tamerj1]

Hi @maneschr2022 

use 

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