Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

COUNTROWS RETURN BLANK AND 0

Hello, I have read that the COUNTROWS function can return either blank or 0. I don't understand why or how it works, if there is a filter context, and for that context there is no row, it makes sense that it returns blank, but in what case would it return 0?

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    You can try this DAX:

    Measure 2 = 
var _a=FILTER(ALL('Table'),[value1]>10)
var _b=COUNTROWS(_a)
return
COALESCE(_b,0)

    Also, it is true that according to what the official documentation states, using only COUNTROWS does not return 0. I'm not sure if this is an error in the documentation or if there is a situation that I haven't tested, I'll continue to try to test this, and I'll reply here if the test is successful.

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    Or you can use this DAX:

    Measure 3 = 
VAR _a = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[value1] > 10

        )
)
RETURN
IF(
    ISBLANK(_a),
    0,
    _a
)

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

13 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      What do you mean by no conditions met? If the are no conditions met, is the same as no rows right?

      • lukiz84's avatar
        lukiz84
        Memorable Member
        2 years ago

        Table = empty: BLANK

        Table != empty, but no conditions met (e.g. filter on Store = 50 but Store values only range from 1-49): 0

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Following this, I have the next problem. Creating a table, and adding a simple measure with countrows, there are empty cells. To solve this I tried to use the function COALESCE but it didnt work. I tried also to sum 0 to the function because BLANK+0=0 but nothing. I dont know how to fix it.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous ,

      You can try this DAX:

      Measure 2 = 
var _a=FILTER(ALL('Table'),[value1]>10)
var _b=COUNTROWS(_a)
return
COALESCE(_b,0)

      Also, it is true that according to what the official documentation states, using only COUNTROWS does not return 0. I'm not sure if this is an error in the documentation or if there is a situation that I haven't tested, I'll continue to try to test this, and I'll reply here if the test is successful.

      Best Regards,
      Dino Tao
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous ,

        Or you can use this DAX:

        Measure 3 = 
VAR _a = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[value1] > 10

        )
)
RETURN
IF(
    ISBLANK(_a),
    0,
    _a
)

        Best Regards,
        Dino Tao
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Anonymous 

    It's essential to highlight that the measure is applied within a matrix visual, where the columns and rows form the coordinating cells for evaluation. In instances where you observe empty or blank spaces, it indicates no evaluation or absence of data records in the table. Consequently, the measure cannot extend to cells without data. I hope this clarification is clear.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      My understanding is that the visual array in each cell acts as a filter context. That way, if there is no data for that context, the measure will act on an empty table, returning a blank. I don't quite understand what you're telling me.

      On the other hand, in the definition of the measure I see that it can return 0 or blank, I don't see when it could return 0, if there is no table then blank, worse when would it return 0?

      Thank you very much for the help

      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        Anonymous 


        Okay, when a calculation returns a zero value it shoudl result in zero