Forum Discussion

mgaut341's avatar
mgaut341
Icon for Helper II rankHelper II
1 year ago
Solved

Counting blank fields based on another column

I am trying to count how many blank cells I have in my 'status' column when my 'appt date/time' field has a value in the cell. I have tried the following formula CALCULATE(COUNT('CCHHS Imaging & Pr...
  • mgaut341's avatar
    mgaut341
    1 year ago

    I was not able to get it to work, so I used a subtraction formula from the other result status' to get my correct answer. 

DataNinja777's avatar
DataNinja777
Icon for Super User rankSuper User
1 year ago

Hi mgaut341 ,

 

To count how many rows have a blank Status while the Appt Date/Time is not blank, you need to use COUNTROWS with a FILTER that checks both conditions. Your current formula only checks if Status is blank, which includes rows even when Appt Date/Time is also blank. The correct DAX formula is:

CALCULATE(
    COUNTROWS('CCHHS Imaging & Procedure'),
    FILTER(
        'CCHHS Imaging & Procedure',
        NOT(ISBLANK('CCHHS Imaging & Procedure'[Appt Date/Time])) &&
        ISBLANK('CCHHS Imaging & Procedure'[Status])
    )
)

This will return the number of rows where Status is blank and Appt Date/Time has a value.

 

Best regards,

mgaut341's avatar
mgaut341
Icon for Helper II rankHelper II
1 year ago

Unfortunately, that tells me I have 0 blank cells which is not accurate as I have 76