COUNTROWS RETURN BLANK AND 0

[storres1]

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?

[v-junyant-msft · Accepted solution]

Hi @storres1 ,

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.

[Fowmy]

@storres1 

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.

[storres1]

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]

@storres1 

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

[storres1]

The measure given that context should act on an empty table and therefore return a BLANK value but it does not. On the other hand, if you use a CARD instead of a table, it works. I have searched for information but can't find anything

[storres1]

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.

[v-junyant-msft · Accepted solution]

Hi @storres1 ,

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.

[v-junyant-msft · Accepted solution]

Hi @storres1 ,

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.

[lukiz84]

Table = empty : blank

No conditions met: 0

[storres1]

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

[lukiz84]

Table = empty: BLANK

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

[lukiz84]

ok, sorry - i just tested it, it's blank in both ways.

[storres1]

Then, the previous solution was not exactly right?

[lukiz84]

no, it was completely wrong, not "not exactly right" 😉 the documentation states it as i did, but dax in excel says different.