COUNTBLANK & IF

[nh27]

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.

BlankStatus = 
IF(
COUNTBLANK('TableA'[Column1] > 0 ||
COUNTBLANK('TableA'[Column2] > 0 ||
COUNTBLANK('TableA'[Column3] > 0,
"Blank",
"Not Blank",
)

The above does not seem to work (I'm getting a too few arguments were passed to the COUNTBLANK function the maximum argue count is 1.

Are there any alternatives?

Thanks

[AlexisOlson]

You're missing closing parentheses before each ">" and have an extra comma before the last parenthesis.

BlankStatus =
IF (
    COUNTBLANK ( 'TableA'[Column1] ) > 0 ||
    COUNTBLANK ( 'TableA'[Column2] ) > 0 ||
    COUNTBLANK ( 'TableA'[Column3] ) > 0,
    "Blank",
    "Not Blank"
)

I don't think this is necessarily the logic you want though. Are you writing a measure or a calculated column?

This should word as a measure to count the number of rows where columns 1, 2, and 3 are all blank in the same row(s).

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

[nh27]

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]

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]

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]

Is this what you mean?

AvailableRowsWithBlankOrZero =
COUNTROWS (
    FILTER (
        'TableA',
	'TableA'[Column5] = "Available" &&
        (
             'TableA'[Column1] = 0 ||
             'TableA'[Column2] = 0 ||
             'TableA'[Column3] = 0
        )
    )
)

[nh27]

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.

[AlexisOlson · Accepted solution]

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]

Hi, sorry me again.

I am trying to use this logic for columns where I have narrative text, however this time I want to count the number of rows where either of column 1 2 or 3 are blank based on the filter of column5. I've tried using = BLANK but it doesn't seem to return any count.

Any ideas?