Forum Discussion

nh27's avatar
nh27
Helper III
2 years ago
Solved

COUNTBLANK & IF

I am trying to return a string that says "Not Blank" or "Blank" if all 3 columns I have in a table are ALL blank, but I'm hitting a bit of a roadblock trying to use an IF statement with COUNTBLANK. ...
Need Help
Show and Tell
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    Hmm. That's what I wrote should do. (Note that it won't work as expected if any parentheses are removed.)

     

    You can do it in multiple steps if you prefer though:

    AvailableRowsWithBlankOrZero =
VAR _AvailableRows_ =
    FILTER (
        'TableA',
        'TableA'[Column5] = "Available"
    )
VAR _NonZeroRows_ =
    FILTER (
        _AvailableRows_,
        'TableA'[Column1] = 0 ||
        'TableA'[Column2] = 0 ||
        'TableA'[Column3] = 0
    )
VAR _Count =
    COUNTROWS ( _NonZeroRows_ )
RETURN
    _Count
nh27's avatar
nh27
Helper III
2 years ago

Thanks,

BlankCount =
COUNTROWS (
    FILTER (
        'TableA',
        ISBLANK ( 'TableA'[Column1] ) &&
        ISBLANK ( 'TableA'[Column2] ) &&
        ISBLANK ( 'TableA'[Column3] )
    )
)

 I think the above is what I need but I am getting no values returned with this, even though I know for sure that there are rows that meet this criteria.

I need the count to be if ANY of the three columns has a 0 or blank. 

e.g. Column A = 0, Column B = 2, Column C = 3

The measure should count this as a blank row record.

The below should NOT be counted as a blank row record:

 Column A = 1, Column B = 2, Column C = 3

Does not seem to work though

AlexisOlson's avatar
AlexisOlson
Super User
2 years ago

If you want to check if ANY are blank rather than ALL are blank, then change the AND operator "&&" to the OR operator "||". Since BLANK() = 0 evaluates as TRUE, you should be able to do the following to check for rows with blank or zero.

 

RowsWithBlankOrZero =
COUNTROWS (
    FILTER (
        'TableA',
        'TableA'[Column1] = 0 ||
        'TableA'[Column2] = 0 ||
        'TableA'[Column3] = 0
    )
)
  • nh27's avatar
    nh27
    Helper III
    2 years ago

    Thank you this works.

    My next challenge is how I can filter this based on filtering another column, e.g. I want to filter Column 5 = "Available" and then apply the above filtering.

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      2 years ago

      Is this what you mean?

      AvailableRowsWithBlankOrZero =
COUNTROWS (
    FILTER (
        'TableA',
	'TableA'[Column5] = "Available" &&
        (
             'TableA'[Column1] = 0 ||
             'TableA'[Column2] = 0 ||
             'TableA'[Column3] = 0
        )
    )
)
      • nh27's avatar
        nh27
        Helper III
        2 years ago

        I don't think that worked unfortunately.

        So basically if Column 5 = Available, then check if any of columns 1, 2, 3 are 0.

        If they are then count that row.

        Almost using a subset of the table where Column 5 = Available is the first filter to pass through.