Forum Discussion

nh27's avatar
nh27
Icon for Helper III rankHelper 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
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
2 years ago

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's avatar
nh27
Icon for Helper III rankHelper 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
    Icon for Super User rankSuper 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
      Icon for Helper III rankHelper 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
        Icon for Super User rankSuper 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
        )
    )
)