topic Re: Counting blank fields based on another column in DAX Commands and Tips

Counting blank fields based on another column

mgaut341 — Fri, 13 Jun 2025 15:48:50 GMT

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

Re: Counting blank fields based on another column

DataNinja777 — Fri, 13 Jun 2025 15:54:38 GMT

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,

Re: Counting blank fields based on another column

mgaut341 — Fri, 13 Jun 2025 16:00:53 GMT

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

Re: Counting blank fields based on another column

v-saisrao-msft — Mon, 16 Jun 2025 09:23:58 GMT

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thank you.

Re: Counting blank fields based on another column

FBergamaschi — Tue, 17 Jun 2025 07:05:45 GMT

Please provide the pbix file for inspection

Best

Re: Counting blank fields based on another column

mgaut341 — Tue, 17 Jun 2025 15:56:44 GMT

Does this work?

No Result Test.xlsx

Re: Counting blank fields based on another column

techies — Tue, 17 Jun 2025 20:50:07 GMT

Hi mgaut341 please try this

Blank Status with Appt =

CALCULATE(

COUNTROWS('Sheet4'),

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

ISBLANK('Sheet4'[Status])

)

Re: Counting blank fields based on another column

mgaut341 — Tue, 17 Jun 2025 20:56:16 GMT

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

Re: Counting blank fields based on another column

techies — Tue, 17 Jun 2025 21:09:43 GMT

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])
)

Re: Counting blank fields based on another column

mgaut341 — Tue, 17 Jun 2025 21:18:29 GMT

This is my result

Re: Counting blank fields based on another column

techies — Tue, 17 Jun 2025 21:30:48 GMT

ok please try this

Blank Status with Appt =

CALCULATE(

COUNTROWS('Sheet4'),

FILTER(

'Sheet4',

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

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

)

Re: Counting blank fields based on another column

mgaut341 — Wed, 18 Jun 2025 14:08:19 GMT

Sadly that doesn't give me the correct answer

Re: Counting blank fields based on another column

Rupak_bi — Thu, 19 Jun 2025 07:16:31 GMT

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

Re: Counting blank fields based on another column

Rupak_bi — Thu, 19 Jun 2025 07:16:33 GMT

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

Re: Counting blank fields based on another column

FBergamaschi — Thu, 19 Jun 2025 08:24:25 GMT

Hi mgaut341

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

Re: Counting blank fields based on another column

v-saisrao-msft — Thu, 19 Jun 2025 09:57:45 GMT

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.

Re: Counting blank fields based on another column

techies — Thu, 19 Jun 2025 16:39:41 GMT

ok, what it's currently shwing?

Re: Counting blank fields based on another column

v-saisrao-msft — Mon, 23 Jun 2025 04:08:21 GMT

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.

Re: Counting blank fields based on another column

mgaut341 — Mon, 23 Jun 2025 14:26:11 GMT

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.

Re: Counting blank fields based on another column

v-saisrao-msft — Tue, 24 Jun 2025 04:16:18 GMT

Hi mgaut341,

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

Thank you.