Forum Discussion
Calculated field from nested dataset challenge!
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.
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 ticketIDIndexGroupedByTicketID = 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 querycheers,
3 Replies
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 ticketIDIndexGroupedByTicketID = 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 querycheers,
Thanks - worked a treat. I noticed the semi-colon didn't work but changed these to commas and it was fine. Thanks again.
Hi,
You may refer to my solution here - Rearrange travel data to clearly show travel from and travel to locations.
Hope this helps.
