Count occurrences of a value when the field can have multiple values

[ChrisR22]

Hello,

I have a dataset that involves tickets closed by employees (see below)

Ticket Assignee	Date Closed
Smith, Bob (ABC123)	1/5/24
Smith, Bob (ABC123), Hill, Jane (DEF456)	1/6/24
Hill, Jane (DEF456), Allen, Peter (GHI789), Smith, Bob (ABC123)	1/8/24

The parenthesis show the employee code. I am looking to do two things:

1. Count the number of times a ticket has more than 1 assignee

2. count the number of tickets each person closes (regardless of whether they are the sole assignee or one of multiple, they should still get a count of 1 for each ticket they are involved with).

Does anyone have any thoughts on how to achieve this? I thought about breaking up the column, but then I would have to have multiple project assignee columns and that would make it difficult to query/slicer for individuals.

Thank you in advance!

[ChrisR22]

@speedramps and still looking for a solution to my original question, how would I count the number of times that a ticket has more than 1 assignee?

Now that I have split all the rows by delimiter, I have a number of project IDs that occur multiple times, so my thinking is to use those. But I am not sure what formula to use as a measure to count the number of times that a single project ID occurs more than once. I don't need to know how many times each ID occurs, just a count of any time a project ID occurs >1 occurance. 

Any thoughts?

[speedramps]

Try this ....

https://1drv.ms/u/s!AgMTUY3Uvq3bg84Cb774_exo-pSkig?e=njo76A 

How it works ...

Use Power Query to add a index as a unqiue Ticket id

In the Transform menu, select Split Column, bu custyomer delimiter

right bracket comma and space "), "

Split at each occurence

Adavance options = Rows

 

You now have a nice friendly table

Select the Ticket Assignee column and Add New column / Column From Examples

Input the first name and artificial intelligences will automaticaly create the logic for you.

Select the Ticket Assignee column and Add Ndew column / Column From Examples

Input the first ticket and artificial intelligences will automaticaly create the logic for you.

Build your report and in the Filter pane just select tickets where the count of assignees is > 1 

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.

[ChrisR22]

@speedramps thank you for the solution. One issue, when split using the below settings as suggested

it left a number of the assignees listed as 

Smith, Bob (ABC123)

Smith, Bob (ABC123

Missing the closed parentheses, meaning I cannot tie the common names together because they are slightly different. Do you know how to prevent it from doing that?