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.

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

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

8 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super 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
      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
    )
)