Measures to find audit log entries that do or don't contain a specific reference first

[Anonymous]

Hi everyone,

I have a table of data (example below), that contains some Reference Numbers, as well as audit log entries relating to each RefNum. I need to create two measures that gives me a count of the reference numbers that have been 'touched' by each Analyst, based on whether a search has been performed, or not performed, first in the ticket. I've put together some simple user stories & functional requirements, as well as example results I might expect, in the hope that it makes this request easier:

RefNum	ActivityType	DateCreated	Analyst
1	Opened	01/09/2019 10:00am	Jim
1	Comment	01/09/2019 10:01am	Jim
1	Search	01/09/2019 10:02am	Jim
1	Search	01/09/2019 10:03am	Barry
2	Opened	01/09/2019 10:04am	Barry
2	Comment	01/09/2019 10:05am	Barry
2	Comment	01/09/2019 10:06am	Barry
2	Search	01/09/2019 10:07am	Jim
3	Opened	01/09/2019 10:08am	Jane
3	Comment	01/09/2019 10:09am	Sally
3	Comment	01/09/2019 10:10am	Jane
3	Comment	01/09/2019 10:11am	Jane
4	Opened	01/09/2019 10:12am	Jane
4	Search	01/09/2019 10:13am	Sally
4	Comment	01/09/2019 10:14am	Jane
4	Search	01/09/2019 10:15am	Jane

• User story#1: I want to see which analysts are not performing search activities. The search only needs to be done once in each ticket though
• Functional requirement #1: Count of all RefNum by each Analyst, that DONT contain a Search activity first ('first' is based on DateCreated)

Results I want to see:

Count of RefNum	Analyst
2	Jim
1	Barry
1	Sally
1	Jane

• User story #2: I want to see which analysts are performing seach activities. The search only needs to be done once in each ticket though
• Functional requirement: Count of all RefNum by each Analyst, but ONLY where tickets contain the FIRST Search activity ('first' is based on DateCreated)

Results I want to see:

Count of RefNum	Analyst
2	Jim
0	Barry
1	Sally
0	Jane

Notes:

• Why not just measure all tickets, you ask? Because the two metrics will be used to identify different opportunities: Either the search-based business process is not being followed (and therefore an analyst might need more training), or it is being performed correctly, and the analyst might receive recognition.
• RefNum is a numeric column, DateCreated is Date\Time, and ActivityType & Analyst are Text.

[v-yuta-msft]

@Anonymous ,

I'm not very clear about "a Search activity first", I suppose that means the first "DateCreated" per Analyst. If so, you can create a Flag column to record which rows can't be count.

Flag = 
IF (
    (
        'Table'[DateCreated]
            = CALCULATE (
                MIN ( 'Table'[DateCreated] ),
                ALLEXCEPT ( 'Table', 'Table'[Analyst] )
            )
    )
        && (
            CALCULATE (
                MIN ( 'Table'[DateCreated] ),
                ALLEXCEPT ( 'Table', 'Table'[Analyst] )
            )
                = CALCULATE (
                    MIN ( 'Table'[DateCreated] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[Analyst] ),
                        'Table'[ActivityType] = "Search"
                    )
                )
        ),
    "Not Count"
)

 

Then create a measure to count the RefNum as below(However the result is not same with the value you provided in the first senario).

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yuta-msft , thanks very much for your response.

Your solution works well, except that it only seems to consider the first RefNum in any one series, rather than performing the calculation for each RefNum in the range. For example, using your solution, the first RefNum has a "Not Count" in it:

But if I add a new 'RefNum' (in this case, "7") with a new earliest 'DateCreated', the "Not Count" disappears from RefNum "1" and appears in "7":

[v-yuta-msft]

@Anonymous ,

So the minimun "datecreated" should be grouped by both Analyst and RefNum, right? You can modify the flag column as below:

Flag = 
IF (
    (
        'Table'[DateCreated]
            = CALCULATE (
                MIN ( 'Table'[DateCreated] ),
                ALLEXCEPT ( 'Table', 'Table'[Analyst], 'Table'[RefNum] )
            )
    )
        && (
            CALCULATE (
                MIN ( 'Table'[DateCreated] ),
                ALLEXCEPT ( 'Table', 'Table'[Analyst], 'Table'[RefNum] )
            )
                = CALCULATE (
                    MIN ( 'Table'[DateCreated] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[Analyst], 'Table'[RefNum] ),
                        'Table'[ActivityType] = "Search"
                    )
                )
        ),
    "Not Count"
)

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yuta-msft , my apologies, I've just realised that this isn't quite achieving what I need yet. Almsot there I think!

I've added a screenshot below showing what searches should be flagged as 'Not Count' (tick marks) and what should not be flagged ('X'). The crosses represent searches that were performed first in each RefNum, and therefore need to be excluded. The tick marks represent searches that were all performed second, chronologically in each ticket, and therefore need to be included as 'Not Count':

Thanks for your time, I really appreciate it.

[Anonymous]

Fantastic @v-yuta-msft , thanks very much for your help 🙂