Getting count based on multiple column criteria (countifs for excel)

Question

Hello!&nbsp;I have a table that has call date, call status and identifer.&nbsp;What I want to do is create a measure that will count rows based on call status and identifer. Identifer is a unique value, but there will be blank rows in that column as well.&nbsp;For example, I need to find how many times call status of "Appointment" and identifer WASN'T blank. Doesn't matter what the value is in identifier, it just cannot be blank.&nbsp;This is what I came up with. It's giving me too many results and not sure why.&nbsp;Appointments = CALCULATE(COUNTROWS(RAW_EXPORT),RAW_EXPORT[CallStatus] = "Appointment",FILTER(RAW_EXPORT,NOT(ISBLANK(RAW_EXPORT[identifier]))))&nbsp;thank you for any help!

lbendlin · Answer

"Identifer is a unique value, but there will be blank rows in that column as well."
&nbsp;
Thank you, I needed that chuckle.
&nbsp;
Since your IDs are supposedly unique you can use this DAX function
&nbsp;
https://docs.microsoft.com/en-us/dax/distinctcountnoblank-function-dax

imthedan · Answer

Haha, I meant the values in indentifer are unique.

imthedan · Answer

That didn't fix the issue. It's still returning the same count as before.

vivran22 · Answer

Hello&nbsp;Imthedan&nbsp;
&nbsp;
You may try this:
Count Not Blank = 
VAR _Filter = 
    FILTER(
        'Table',
            NOT ISBLANK('Table'[Identifier])
                &amp;&amp; 'Table'[Call Status] = "Appointment"
    )
VAR _Count = 
 CALCULATE(COUNTROWS('Table'),_Filter)
RETURN
_Count
&nbsp;
Cheers!VivekIf it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)Blog: vivran.in/my-blogConnect on LinkedInFollow on Twitter

imthedan · Answer

Sorry for the late response! I was out of town for the week avoiding the smoke from the wildfires in my area.

I appreciate the help! Would it be possible to get that in a DAX solution? I am trying to force myself to learn it and knowing how to count a condition based on mutliple criteria in DAX would go a long way for me.

Thank you!

Forum Discussion

Getting count based on multiple column criteria (countifs for excel)

9 Replies

Related content

Countif with multiple criteria

Sequential Order column based on multiple criteria

Countif with multiple criteria - DAX

COUNTIF multiple column criteria selections

DAX FILTER with multiple criteria