Summary table based on activity logs

[Anonymous]

Hi everyone, I'm hoping someone can assist with a query. I have two tables with lots of data - 1 million+ lines in each one.

Table 1

Each line in this table shows a ticket ID (RefNum) and the resolver group that is currently assigned to that ticket (AssignedGroup). Each line is in no particular order. Example below:

RefNum	AssignedGroup
1054675	Networks
1054976	Application Support
1251685	Global Delivery
1290806	Service Desk
1294302	Global Delivery
1296581	Service Desk

Table 2

Each line in this table shows a ticket ID (RefNum), activities that have occurred within each ticket (ActivityType), and a datestamp for that activity (DateActivityCreated). There may be multiple lines for each RefNum, and each line is in no particular order. Table 2 has a relationship to Table 1 via the 'RefNum' field. Example below:

RefNum	ActivityType	Transferred From	Transferred To	DateActivityCreated
1054675	Reopened			19/02/2019 9:58
1054675	Resolved			19/02/2019 9:59
1054675	Event Logged			30/04/2019 8:44
1054675	Group Transferred	Service Desk	Networks	11/07/2019 12:12
1054976	Event Logged			19/02/2019 9:59
1054976	Group Transferred	Service Desk	Application Support	19/02/2019 9:59
1251685	Event Logged			11/07/2019 12:11
1290806	Reopened			7/02/2019 14:05
1290806	Group Transferred	Application Support	Global Delivery	7/02/2019 14:05
1290806	Group Transferred	Global Delivery	Desktop	7/02/2019 14:06
1290806	Event Logged			7/02/2019 14:06
1290806	Status Changed			7/02/2019 14:06
1290806	Status Changed			7/02/2019 14:07
1290806	Group Transferred	Desktop	Service Desk	7/02/2019 14:09
1290806	Resolved			7/02/2019 14:09
1294302	Event Logged			15/03/2019 14:40
1296581	Field Updated			11/03/2019 13:57
1296581	Field Updated			11/03/2019 13:57
1296581	Event Logged			11/03/2019 13:57

Requirement

I'd like to be able to create a summary table that shows me a unique list of 'RefNum' numbers, showing where tickets are assigned at the point of SLA failure. SLA failure is signified by 'Event Logged' in Table 2.

Examples:

Here's what I would expect to see:

RefNum	Group Expired
1054675	Service Desk
1054976	Service Desk
1251685	Global Delivery
1290806	Desktop
1294302	Global Delivery
1296581	Service Desk

So for example, RefNum 1054675 is showing 'Networks' as the current AssignedGroup in the summary table because that is who the RefNum was last 'Transferred To' in Table 2. But the first 'Event Logged' for RefNum 1054675 occurrs when the ticket is still with the 'Service Desk' and before 1054675 is transferred to Networks, so the summary table should show 'Service Desk' as the 'Group Expired'.

In the case of 1296581, no Group Transfer has taken place. So the 'Group Expired' column in the summary table should be 'Service Desk'.

In the case of 1290806, the 'Event Logged' occurred after the last 'Group Transferred' activity to 'Desktop', so the 'Group Expired' column in the summary table should be 'Desktop'.

Note: 'Last', 'first', 'after' etc. are all determined by the 'DateActivityCreated' column.

Any help would be appreciated!

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can try to add a new calculated table to get your expected result.

Table 2 =
ADDCOLUMNS (
    DISTINCT ( 'RefNum'[RefNum] ),
    "Group Expired",
    VAR t =
        FILTER (
            'RefNum',
            'RefNum'[RefNum] = EARLIER ( [RefNum] )
                && TRIM ( 'RefNum'[Transferred From] ) <> ""
                && TRIM ( 'RefNum'[Transferred To] ) <> ""
        )
    RETURN
        IF (
            COUNTROWS ( t ) = 0,
            MAXX (
                FILTER ( 'Table1', 'Table1'[RefNum] = EARLIER ( [RefNum] ) ),
                [AssignedGroup]
            ),
            MAXX (
                FILTER (
                    t,
                    'RefNum'[Transferred To]
                        = MAXX (
                            FILTER ( 'Table1', 'Table1'[RefNum] = EARLIER ( [RefNum] ) ),
                            [AssignedGroup]
                        )
                ),
                [Transferred From]
            )
        )
)

Best Regards,
Jack Chen

[Anonymous]

Hi @Anonymous  ,

Please clarify the following:

In the case where ActivityType Event Logged and Group Transferred have the exact same value for DateActivityCreated, how can you differentiate which activity was done last? 

For example below, 

1290806	Group Transferred	Global Delivery	Desktop	7/02/2019 14:06
1290806	Event Logged			7/02/2019 14:06

Any chance you can format your DateActivityCreated column to show the seconds as well?

Cheers,
Robin