cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mhutchens81 Regular Visitor
Regular Visitor

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

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:

 

RefNumActivityTypeDateCreatedAnalyst
1Opened01/09/2019 10:00amJim
1Comment01/09/2019 10:01amJim
1Search01/09/2019 10:02amJim
1Search01/09/2019 10:03amBarry
2Opened01/09/2019 10:04amBarry
2Comment01/09/2019 10:05amBarry
2Comment01/09/2019 10:06amBarry
2Search01/09/2019 10:07amJim
3Opened01/09/2019 10:08amJane
3Comment01/09/2019 10:09amSally
3Comment01/09/2019 10:10amJane
3Comment01/09/2019 10:11amJane
4Opened01/09/2019 10:12amJane
4Search01/09/2019 10:13amSally
4Comment01/09/2019 10:14amJane
4Search01/09/2019 10:15amJane

 

  • 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 RefNumAnalyst
2Jim
1Barry
1Sally
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 RefNumAnalyst
2Jim
0Barry
1Sally
0Jane

 

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.
5 REPLIES 5
Community Support Team
Community Support Team

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

@mhutchens81 ,

 

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"
)

1.PNG 

 

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).

2.PNG 

 

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.

 

 

 

mhutchens81 Regular Visitor
Regular Visitor

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

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:
FirstImage.PNG

 

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":

SecondImage.PNG

Community Support Team
Community Support Team

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

@mhutchens81 ,

 

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"
)

Capture.PNG 

 

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.

mhutchens81 Regular Visitor
Regular Visitor

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

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

mhutchens81 Regular Visitor
Regular Visitor

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

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':

 

Capture.PNG

 

Thanks for your time, I really appreciate it.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)