Figuring out length of first reponse on tickets.

[JoshP]

I have some good experience with Power BI under my belt but still struggle with the complicated DAX stuff, so I appeal for your help.  I also don't really have any code to start with because I don't know how to begin.  I've attempted a few google searches but haven't found a good start yet.

Like many others, I have Power BI plugged into my ticketing database.  I have two tables in question, one that holds my overall ticket info (most importantly, the ticket number/ID) and another that holds data on changes made to the ticket.  I've got the relationship set between these two tables without issue and can ultimately create a table that looks like the following:

Ticket ID	Ticket Creation Timestamp	Ticket Change Timestamp
21223	11/24/2023 7:30:24 AM	11/24/2023 7:30:24 AM
21223	11/24/2023 7:30:24 AM	11/24/2023 8:12:03 AM
21223	11/24/2023 7:30:24 AM	11/24/2023 2:38:30 PM
21223	11/24/2023 7:30:24 AM	11/25/2023 10:13:09 AM
21224	11/26/2023 9:13:58 AM	11/26/2023 9:13:58 AM
21224	11/26/2023 9:13:58 AM	11/26/2023 12:48:42 PM
21224	11/26/2023 9:13:58 AM	11/26/2023 3:12:47 PM
21225	11/27/2023 11:38:02 AM	11/27/2023 11:38:02 AM
21225	11/27/2023 11:38:02 AM	11/28/2023 8:03:41 AM
21225	11/27/2023 11:38:02 AM	11/29/2023 10:47:38 AM

All just made up info but basically what I'm showing is that each ticket can and will have multiple change timestamps.  Any time we do something to the ticket, it creates a new database entry indicating such.  I want to measure how long it's taking us to do a first-touch on each ticket.  That first-touch is essentially the second change timestamp for each ticket, all others can be ignored because I only care about the very first change (other than the initial creation).

Any help is appreciated on how to accomplish this because so far I'm coming up empty.  After figuring this out, I'll then be trying to figure out how to only count business hours in the equation, since we're not a 24/7 shop and do not want to be counting tickets that came in late in the day and weren't touched until first thing the next morning.

[JoshP]

Wanted to post some progress and hoping someone is able to point me in the right direction to finish this off.  Since my earlier reply, I've updated the "isChange" column to one that is comparing the ticket creation column and all change timestamps column.  As stated previously, the first change timestamp on each ticket ID is when it was created, so I'm ignoring that one.  Here's a screenshot of that:

I've also been able to figure out a measure that is grabbing the FIRSTNONBLANK value in the [HoursBetweenChange] column for a unique ticket ID:

What I'm now trying to finish off is being able to average the FirstTouch value.  As you can see, it just orders them by size and the "total" is the last one.  How can I end up with a single card/KPI showing me a value based on a date range set in the filters?  Still working on that but if anyone has any ideas, I'm all ears.

For anybody it might help, below is my code for both the HoursBetweenChange column and the FirstTouch measure:

HoursBetweenChange = 
    VAR CreatedTimeStamp = CALCULATE(MIN('ORG1 HD_TICKET'[CREATED]))
    VAR ChangeTimeStamp = CALCULATE(MIN('ORG1 HD_TICKET_CHANGE'[TIMESTAMP]))

    RETURN
    IF ( CreatedTimeStamp <> ChangeTimeStamp,
        DATEDIFF(CreatedTimeStamp,'ORG1 HD_TICKET_CHANGE'[TIMESTAMP], MINUTE) -
        (
            CALCULATE (
                COUNTROWS('WeekDay_Calendar'),
                'WeekDay_Calendar'[WeekdayTF] = FALSE(),
                DATESBETWEEN('WeekDay_Calendar'[Date], CreatedTimeStamp,'ORG1 HD_TICKET_CHANGE'[TIMESTAMP])
            )
        )
    ) / 60

FirstTouch = FIRSTNONBLANK('ORG1 HD_TICKET_CHANGE'[HoursBetweenChange], MIN('ORG1 HD_TICKET_CHANGE'[HD_TICKET_ID]))

[gmsamborn]

Hi @JoshP 

Would something like this help?

First Response = 
VAR _Tkt = SELECTEDVALUE( 'Table'[Ticket ID] )
VAR _Create = SELECTEDVALUE( 'Table'[Ticket Creation Timestamp] )
VAR _Curr = SELECTEDVALUE( 'Table'[Ticket Change Timestamp] )
VAR _2nd =
    CALCULATE(
        MIN( 'Table'[Ticket Change Timestamp] ),
        FILTER(
            ALL( 'Table' ),
            'Table'[Ticket ID] = _Tkt
                && 'Table'[Ticket Change Timestamp] > _Create
        )
    )
VAR _Diff = CONVERT( _2nd - _Create, DOUBLE )
VAR _Result =
    IF(
        _Curr = _2nd,
        FORMAT( _Diff, "hh:mm:ss" )
    )
RETURN
    _Result

There is probably a better way but this is what came to mind for me.  Let me know how it goes.

First Response.pbix

[JoshP]

I think I'm making some progress in the right direction, maybe.  I was able to create a new column that successfully compares the time the ticket was created to the times that any changes were made, and indicates whether the timestamp was an actual change or not.

isChange = 
    VAR FirstTimeStamp = CALCULATE(MIN('TABLENAME'[CREATED]))
    VAR SecondTimeStamp = CALCULATE(MIN('TABLENAME'[TIMESTAMP]))

    RETURN
    IF (
        FirstTimeStamp == SecondTimeStamp,
        "no",
        IF (
            FirstTimeStamp <> SecondTimeStamp,
            "yes"
        )
    )

I'm thinking that this might help me more easily key from the first yes of each ticket number and compare to when it was created.  Still trying to figure out how to do that, if anybody has any tips.