Forum Discussion

maneschr2022's avatar
maneschr2022
Helper II
4 years ago
Solved

Count text values and return 0 if blank

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!

  • tamerj1's avatar
    tamerj1
    4 years ago

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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      zendu
      Microsoft Employee
      3 years ago

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi maneschr2022 

    use 

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

  • maneschr2022's avatar
    maneschr2022
    Helper II
    4 years ago

    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.

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

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