Count first occurrence of string only (new requirements)

[Anonymous]

This is a new request based on this solved query.

Original request details:

"
I have a db with a few million ITSM tickets. Each of those tickets has a Reference Number, as well as some Activity data, datestamps, plus the name of the person who has performed the activity.

I want to be able to count how many times each person has performed a 'KT Solution Linked' activity on each ticket, but only if there isn't already a 'KT Solution Linked' activity in the ticket. I've mocked up a table with some example data, the measures I'm currently using ("Old Stats") as well as what I might expect to see "New Stats").

https://imgur.com/a/Z2bQfPq
"

Refinements in the thread above:

"
I'd like to be able to create a 'Link %' measure, which would be calculated as ([Measure 2] / [Tickets Touched] * 100).
"

New requirements:

"
I'd like to come up with a number that gives me a distinct count of tickets from the result, rather than counting both strings individually. So for example, if string 1 and string 2 are both present from the same analyst in the same ticket, it only counts as a single increment. But if string 1 and string 2 are present from the same analyst in separate tickets, they would count as two increments.

And this is still in the context of only wanting to count each string if those strings aren't already in the ticket, so if string 1 and string 2 are both present from the same analyst in the same ticket, but there is also already another string 1 in the same ticket from a different analyst that occurred previously, then that would only count as a single increment.
"

Hopefully that makes sense. I'd really appreciate any assistance that can be provided.

[v-jiascu-msft]

Hi @Anonymous,

Can you post the expected result, please? Please also post an example to show the process of the calculation. When I dig into the requirements, I have a few questions.

1. "it only counts as a single increment." Who should be the owner of that increment?

2. What's the ticket? Is it the [RefNum]?

Best Regards,

[Anonymous]

Hi @v-jiascu-msft,

No problem, and thanks for getting back to me. I have mocked up some data and expected results in a spread sheet. Columns A-D is the ticket data, colour-coded by ticket number. RefNum is the unique ID number. There are lots of individual activities that occur in each ticket, by multiple Analysts.

Columns F-K of the spread sheet are the 'Expected Results' I would expect from the counts ("Count of Knowledge Trail" and "Count of Knowledge Linked"). I have colour-coded the Expected Results to match the colours of the tickets on the left, and they incrememt as you move down, i.e. the dark green 'Expected Results' is the total from the dark green ticket data, then the yellow 'Expected Results' is the total from the yellow ticket data as well as the preior dark greet ticket data, and so on. Here are the general rules I'm working with:

    -To separately count the occurrence of two activities in ITSM ticket log data: "Knowledge Trail" and "Knowledge Linked";
    -To only count those occurrences, if they haven't previously occurred in the ticket;
    -To only count 'Knowledge Trail' if there isn't a "Knowledge Linked" occurrence in the ticket already;
    -To display results in a table, broken down by analyst.

[Anonymous]

To try and help progress this, I've mocked up a report - link to PBI file is here. The report contains the raw data in a table, expected results in an image, and current results (i.e. what I'm getting at the moment) in a final table.

I've used separate 'Tickets Touched' measures for 'Knowledge Trail' and 'Knowledge Linked', because I think that'll make it easier to come up with the right measures.

I've highlighted in blue the fields that I think are working correctly. The non-highlighted fields I don't think are working correctly.