Counting blank fields based on another column

[mgaut341]

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 & Procedure'[Appt Date/Time]), FILTER('CCHHS Imaging & Procedure','CCHHS Imaging & Procedure'[Status]=""))

but it gives me the total number of blanks in the Status column regardless of if the Appt Date/Time column has a value 

[v-saisrao-msft]

Hi @mgaut341,

checking in to see if your issue has been resolved. And share the sample data or Pbix file.
Please let us know if you still need assistance.

Thank you.

[mgaut341 · Accepted solution]

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. 

[v-saisrao-msft]

Hi @mgaut341,

Glad your issue has been resolved. Please mark your reply as the solution, as it will help other community members.

Thank you.

[v-saisrao-msft]

Hi @mgaut341,

I can't access your PBIX file. The error message indicates it does not exist in the tenant, so please share the sample file with access.

Thank you.

[Rupak_bi]

Hi @mgaut341 ,

unable to get the sample data. However, to solve this,

Create a calculated column with below syntax and get a sum. 

column = if(and(date/time <> blank()   ,   status = blank())  , 1, blank())

this flag column should put 1 in case of status blank and date/time not blank. 

then you need to sum it up. 

Let me know if this works. Else, share sample data

[Rupak_bi]

Hi @mgaut341 ,

unable to get the sample data. However, to solve this,

Create a calculated column with below syntax and get a sum. 

column = if(and(date/time <> blank()   ,   status = blank())  , 1, blank())

this flag column should put 1 in case of status blank and date/time not blank. 

then you need to sum it up. 

Let me know if this works. Else, share sample data

[techies]

Hi @mgaut341 please try this

Blank Status with Appt =

CALCULATE(

    COUNTROWS('Sheet4'),

    NOT(ISBLANK('Sheet4'[Appt Date/Time])),

    ISBLANK('Sheet4'[Status])

)

[mgaut341]

That shows up as 0, not the 77 I am looking for

[techies]

ok, create this calculated column to see the field is blank

Status Debug =
IF(
ISBLANK('Sheet4'[Status]),
"True Blank",
"[" & 'Sheet4'[Status] & "] LEN=" & LEN('Sheet4'[Status])
)

[mgaut341]

This is my result 

[techies]

ok please try this

Blank Status with Appt =

CALCULATE(

    COUNTROWS('Sheet4'),

    FILTER(

        'Sheet4',

        NOT(ISBLANK('Sheet4'[Appt Date/Time])) &&

        LEN(TRIM('Sheet4'[Status])) = 0

    )

)

[mgaut341]

Sadly that doesn't give me the correct answer

[techies]

ok, what it's currently shwing?

[FBergamaschi]

Please provide the pbix file for inspection

Best

FB

[mgaut341]

Does this work?

No Result Test.xlsx

[FBergamaschi]

Hi mgaut341

No as  I am not allowed in your Tenant, can you attach the pbix here in your reply?

[v-saisrao-msft]

Hi @mgaut341,

Please provide sample PBIX file that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? 

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thank you.

[DataNinja777]

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]

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