How to calculate number of non blank rows based on the value using dax

Question

I have a table with numeric values and blank records. I'm trying to calculate a number of rows that are not blank and bigger than 20.

+--------+
| VALUES |
+--------+
|      2 |
|      0 |
|     13 |
|     40 |
|        |
|      1 |
|    200 |
|      4 |
|    135 |
|        |
|     35 |
+--------+

I've tried different options but constantly get the next error: "Cannot convert value '' of type Text to type Number". I understand that blank cells are treated as text and thus my filter (>20) doesn't work. Converting blanks to "0" is not an option as I need to use the same values later to calculate AVG and Median.

CALCULATE(
    COUNTROWS(Table3),
    VALUE(Table3[VALUES]) > 20
    )

OR getting "10" as a result:

=CALCULATE(
    COUNTROWS(ALLNOBLANKROW(Table3[VALUES])),
    VALUE(Table3[VALUES]) > 20
    )

The final result in the example table should be: 4

Would be grateful for any help!

Alexis Olson Alexis Olson · Accepted Answer · 2019-01-15T22:15:11

First, the VALUE function expects a string. It converts strings like "123"into the integer 123, so let's not use that.

The easiest approach is with an iterator function like COUNTX.

CountNonBlank = COUNTX(Table3, IF(Table3[Values] > 20, 1, BLANK()))

Note that we don't need a separate case for BLANK() (null) here since BLANK() > 20 evaluates as False.

There are tons of other ways to do this. Another iterator solution would be:

CountNonBlank = COUNTROWS(FILTER(Table3, Table3[Values] > 20))

You can use the same FILTER inside of a CALCULATE, but that's a bit less elegant.

CountNonBlank = CALCULATE(COUNT(Table3[Values]), FILTER(Table3, Table3[Values] > 20))

Edit

I don't recommend the CALCULATE version. If you have more columns with more conditions, just add them to your FILTER. E.g.

CountNonBlank =
COUNTROWS(
    FILTER(Table3,
        Table3[Values] > 20
            && Table3[Text] = "xyz"
            && Table3[Number] <> 0
            && Table3[Date] <= DATE(2018, 12, 31)
    )
)

You can also do OR logic with || instead of the && for AND.

How to calculate number of non blank rows based on the value using dax

1 Answers