Tickets sent to and from a specific team

[jimminy]

Hi 🙂

I am working through a probem where we have tickets being sent between departments, sometime multiple times a day. My goal is to create a chart that shows the number of tickets sent to/from a team based on a team filter. So the chart would change based on what team is selected in the filter. I have the date table set, but I am struggling with how to count the number of times a ticket sent to/from a given team ID. 

The fact table consists of time stamps for every stage of the ticket.

Sample data:

Ticket ID	Team ID	Start Date/Time	End/Date Time
111	1	01/01/2022 10.04.21	01/02/2022 13.34.10
111	2	01/02/2022 13.34.10	01/05/2022 12.56.03
111	1	01/05/2022 12.56.03	01/07/2022 10.30.45
222	1	01/04/2022 09.43.10	01/04/2022 10.15.15
222	2	01/04/2022 10.15.15	01/15/2022 11.12.45
222	2	01/15/2022 11.12.45	02/01/2022 14.35.37
222	1	02/01/2022 14.35.37	02/15/2022 11.21.07

Ultimately I would like to show that Team 1 in January has sent two tickets and received one. For Team 2 the result would be that they've sent two tickets and received two.

If I'm missing some important info of course let me know and I'll provide. 

Looking forward to any suggestions!

Best,

J

[ERD]

Hello @jimminy ,

I don't know all the particularities of your file, model, expected visuals, etc., but one of the ways is to create calculated columns to define if the ticket was sent/received and measures to count these events.

1. You need to have a proper Date table. So I suppose you do.

2. You've mentioned a "Group Rank" column, so I've created one as well:

Step = 
VAR current_ticketID = 'Table'[Ticket ID]
VAR tickets =
    FILTER ( 'Table', 'Table'[Ticket ID] = current_ticketID )
RETURN
    RANKX ( tickets, [Start Date/Time],, ASC )

3. Send/Receive calculated columns:

Send = 
VAR current_date = 'Table'[Start Date/Time]
VAR current_ticketID = 'Table'[Ticket ID]
VAR next_date =
    CALCULATE (
        MIN ( 'Table'[Start Date/Time] ),
        FILTER (
            'Table',
            'Table'[Start Date/Time] > current_date && 'Table'[Ticket ID] = current_ticketID
        )
    )
VAR next_teamID =
    CALCULATE (
        MIN ( 'Table'[Team ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Start Date/Time] = next_date )
    )
RETURN
    SWITCH (
        TRUE (),
        'Table'[Step] = 1, 'Table'[Team ID],
        'Table'[End/Date Time] <> BLANK ()
            && next_teamID <> BLANK ()
            && next_teamID <> 'Table'[Team ID], 'Table'[Team ID],
        BLANK ()
    )

Receive = 
VAR current_date = 'Table'[Start Date/Time]
VAR current_ticketID = 'Table'[Ticket ID]
VAR previous_date =
    CALCULATE (
        MAX ( 'Table'[Start Date/Time] ),
        FILTER (
            'Table',
            'Table'[Start Date/Time] < current_date && 'Table'[Ticket ID] = current_ticketID
        )
    )
VAR previous_teamID =
    CALCULATE (
        MAX ( 'Table'[Team ID] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Start Date/Time] = previous_date )
    )
RETURN
    IF (
        'Table'[Step] <> 1 && 'Table'[Team ID] <> previous_teamID,
        'Table'[Team ID]
    )

They indicated a team id if it has sent and/or received a ticket. Please, take into account that I used a Start date column only for the events occurence. I've also used this column to create another one (Date only) and connect it to the Date table.

4. Create measures to count events:

Sent = COUNT('Table'[Send])

Received = COUNT('Table'[Receive])

Result: 

[daXtreme]

Hi @jimminy 

Where is the information about who sent and who received the same ticket? Maybe it'd be useful to add this information as a column with statuses of (Ticket Sent, Ticket Received) to the table? It would make life (and coding) definitely easier.

[jimminy]

Hi @daXtreme ,

Unfortunetly the data set does not include this information outright, but it comes in the form of sequence like depicted above. So we can assume that the "flow" of the ticket is such. If the team changes from 1 to 2 for ticket id 111, then the ticket is sent from 1 to 2.

I should note I've also included a "Group Rank" column for each ticket in my file, so that rank 1 indicates the first step and so on.

Does this help?

Best,

J

[daXtreme]

Hi @jimminy 

The fact that the data does not contain this info in a clear form does not mean you can't add this via Power Query! You certainly can. Enriching the data with such info would be tremendously beneficial.

If I get a chance, I'll try to do it in PQ and post the code in here.

The problem with @ERD's code is that it's... well, just too complex (for my taste). This precisely stems from the fact that the info I've asked about (and some more that can be deduced from the data and put in the table in a clear form) is missing from the table.

[jimminy]

I'm not so familiar with Power Query so any input is appreciated!

[daXtreme · Accepted solution]

File attached. Be prepared to see some serious M code... You'll have to adjust things here and there because I did not know how to handle the starts of ticket flows and their ends. Should they be treated as the Sent/Received actions or should they belong to their own special categories? You have not specified this... so I've assumed the event "Received" also applies to the beginning of a flow. Similarly for the "Sent" event.

[jimminy]

hi @daXtreme ,

Thanks a million. I have adjusted the code and i've been able to visualize the sent and received tickets without bogging things down with DAX.

Best,

J