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

[Imthedan]

Hello!

I have a table that has call date, call status and identifer.

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.

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.

This is what I came up with. It's giving me too many results and not sure why.

Appointments = CALCULATE(COUNTROWS(RAW_EXPORT),RAW_EXPORT[CallStatus] = "Appointment",FILTER(RAW_EXPORT,NOT(ISBLANK(RAW_EXPORT[identifier]))))

thank you for any help!

[vivran22]

Hello @Imthedan 

You may try this:

Count Not Blank = 
VAR _Filter = 
    FILTER(
        'Table',
            NOT ISBLANK('Table'[Identifier])
                && 'Table'[Call Status] = "Appointment"
    )
VAR _Count = 
 CALCULATE(COUNTROWS('Table'),_Filter)
RETURN
_Count

Cheers!
Vivek

If 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-blog
Connect on LinkedIn
Follow on Twitter

[Imthedan]

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!

[vivran22]

@Imthedan 

Sorry to know about your situattion. Hope everything is fine now.

The solution I had shared is a for creating Measures using DAX. If you can share more details on your requirement/problem, then I can share more specific solution.

Feel free to connect with me.

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Imthedan]

Vivran22,

I tried adding the code and it looks like it's not filtering the column for counting non blank rows correctly. There should be 15 results, but it's returning 34 (the total appointments for everything).

What I am looking to do:

Call Date	Status	Transaction ID
8/24/20	Appointment Set	12591258
8/24/20	Dead
8/24/20	Appointment Set
8/24/20	Waiting	326236236
8/24/20	Appointment Set
8/24/20	Appointment Set	9329665

What I need is to count the rows that have an Appointment Set & have a transaction ID. In the above example, it should count two rows. The issue I ran into using the code you gave me and the countax fomula is that it's counting all "appointment sets" for the day regardless if the transaction id row has a value or not. So, it is returning 4 rows in the above example instead of the 2 that it should.

I tried the code you gave me, and one that used:

 COUNTAX(FILTER('TABLE',[Status]="Appointment Set"),[Transaction ID])

I need it done this way, because I have multiple transaction id columns (basically signifing the source of the click/call). The idea is to have measures that count the rows for appointments (and other statuses) that were generated from transaction ID1, 2 and 3.

If you have any other questions or need a better example then let me know. Thank you!

[Imthedan]

I might have just figured out a way to do it.

I just added  a custom column that if the LEN of the transaction ID column is greater than 1, then add value "transactionid" --

Then I just did:

Appointment = CALCULATE(COUNTROWS(Table),Table[status] = "Appointment Set",Table[trans_test] = "trans")

This appears to be counting correctly. I was hoping for a way that didn't involve adding new columns, but this seems to work.

Thank you!

[vivran22]

@Imthedan 

I have used the DAX measure shared in the earlier response, and it is giving the desired results:

I am not sure why it is not working for you.

What you need is possible without a calculated column.

Cheers!
Vivek

If 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-blog
Connect on LinkedIn
Follow on Twitter

[lbendlin]

"Identifer is a unique value, but there will be blank rows in that column as well."

Thank you, I needed that chuckle.

Since your IDs are supposedly unique you can use this DAX function

https://docs.microsoft.com/en-us/dax/distinctcountnoblank-function-dax

[Imthedan]

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

[Imthedan]

Haha, I meant the values in indentifer are unique.