Calculated field from nested dataset challenge!

[grussell]

Hi,

I'm stuggling - need some guidance!

I am trying to map our helpdesk ticket data into the sankey visuals.

Out ticket data shows how tickets are moved from one engineer to the next and is a simplified structureed looks like this:

TicketID	Date	User
INC0001	01/01/2019 09:00	John
INC0003	03/01/2019 09:35	Matt
INC0001	01/01/2019 12:10	Phil
INC0005	04/01/2019 09:00	John
INC0003	03/01/2019 14:00	Phil
INC0006	05/01/2019 13:00	John
INC0006	06/01/2019 11:05	Matt
INC0003	04/01/2019 09:10	John

For instance, the above example shows INC001 starts with John, then Phil.

I am trying to map the ticket flows within a Sankey report which wants the data like this:-

TicketID	Source	Destination
INC0001	John	Phil
INC0003	Matt	Phil
INC0003	Phil	John
INC0006	John	Matt

I've been trying to FILTER matching the ticketID but I cant work out how to ensure it is working cronologically through the dates.

Help!!

Gavin.

[Ashish_Mathur]

Hi,

You may refer to my solution here - Rearrange travel data to clearly show travel from and travel to locations.

Hope this helps.

[sturlaws · Accepted solution]

Hi @grussell ,

this can be handled both with DAX and M(power query). My M-skills are not the best, so I can show the DAX-version

Using you the sample data you provided, I create a calculated column to get an index grouped per ticketID

IndexGroupedByTicketID =
VAR _ticket = 'Table'[TicketID]
VAR _date = 'Table'[Date]
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' );
        FILTER (
            ALL ( 'Table' );
            'Table'[TicketID] = _ticket
                && 'Table'[Date] <= _date
        )
    )

Using this column to find the next person to handle the ticket

NextUser =
VAR _ind = 'Table'[IndexGroupedByTicketID] + 1
VAR _ticket = 'Table'[TicketID]
RETURN
    CALCULATE (
        MIN ( 'Table'[User] );
        FILTER (
            ALL ( 'Table' );
            'Table'[IndexGroupedByTicketID] = _ind
                && 'Table'[TicketID] = _ticket
        )
    )

In the sankey chart, put user in the source field and nextUser in the destination field

Depending on the size of your dataset and you source, there is possibly some performance to gain by doing this in power query. The principle is the same for doing it in power query, here is a link to how to build a similar index in power query

cheers,

[grussell]

Thanks - worked a treat. I noticed the semi-colon didn't work but changed these to commas and it was fine. Thanks again.