COUNTROWS - Not counting as expected.

[DANNYBLACK]

Hi

I am pulling my hair out with this... (FYI new to PowerBI)

I am trying to count the number of rows that have a value where RED - I am expecting the measure to return 6 but it is doing a SUM rather than counting and returning 19!

NUMBER OF RED = COUNTROWS(FILTER('Page 1','Page 1'[RED]>0))

FYI RED is a measure in itself

RED = 
CALCULATE(
COUNTROWS('Page 1'),'Page 1'[Residual Score] = "RED",
ALLSELECTED('Page 1'[Entity])
) + 0

Any help would be greatly apprecaited. 🙂

Thanks - Danny

[v-kkf-msft · Accepted solution]

Hi @DANNYBLACK ,

Please try the following formula:

Measure = 
COUNTX ( FILTER ( VALUES ( 'Page 1'[Subcategory] ), [RED] > 0 ), [RED] )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[truptis]

@DANNYBLACK -> Since RED is a measure, it is giving us the error. Because COUNT supports a column. eg- COUNT([columnname]).
If you could tell what exactly you are expecting in RED measure, we can make RED as a column and figure out

[truptis]

@DANNYBLACK -> How are you deriving [RED]? what formula are you using?

[DANNYBLACK]

Hi @truptis 

RED is a MEASURE

RED = 
CALCULATE(
COUNTROWS('Page 1'),'Page 1'[Residual Score] = "RED",
ALLSELECTED('Page 1'[Entity])
) + 0

I just need to count the number of rows where RED has a value > 0

[DANNYBLACK]

@amitchandak 

NUMBER OF RED = SUMX(VALUES('Page 1'[Subcategory]), 'Page 1'[RED])

This still returns a value of 19 (expectation is 6)

@truptis 

So sorry, unless I have complely misunderstood neither of thoes worked. 

Any other ideas?

[amitchandak]

@DANNYBLACK , if Red is a measure then try like

Sumx(values(table[Sub Category]), [RED])

[truptis]

Hi @DANNYBLACK ,
Try using this -

Result = Calculate(count([Red]), filter(tablename, tablename[Red] <> 0)