Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Find time between one fixed row and one variable row

Hello, I hope some can help me, i have a data table with this columns

- TicketID

- Ticket creation date

- Last action

- Date/time of last action

 

Actions can be things like giving a priority to the ticket or give a solution to the customer. For every action in a ticket a new table row is created.

 

I want to create something that shows me the average time between the giving a priority to a ticket action and the first action that follows on giving a priority to a ticket. The difficulty is that the action that follows on giving a priority to a ticket varies.

 

Can someone help me with how to get this done?

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @Anonymous,

 

It looks like there existing more than one records where Action is set to "Set Priority to regular" per Ticket ID. If so, please try below suggestion. 

 

First, add a calculated column in Table1.

Index =
RANKX (
    FILTER ( 'Table1', Table1[TicketID] = EARLIER ( Table1[TicketID] ) ),
    Table1[Action date/time],
    ,
    ASC,
    DENSE
)

Then, add measure [Average] into a card visual.

Timediff =
VAR _CurrentActiontime =
    SELECTEDVALUE ( Table1[Action date/time] )
VAR _NextActiontime =
    CALCULATE (
        SELECTEDVALUE ( Table1[Action date/time] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[TicketID] ),
            Table1[Index]
                = MAX ( Table1[Index] ) + 1
        )
    )
VAR diff =
    IF (
        SELECTEDVALUE ( Table1[Action] ) = "Set priority to regular",
        DATEDIFF ( _CurrentActiontime, _NextActiontime, SECOND ),
        BLANK ()
    )
RETURN
    diff

Average =
AVERAGEX ( ALLSELECTED ( Table1 ), [Timediff] )

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
v-yulgu-msft
Employee
Employee

Hi @Anonymous,

 

It looks like there existing more than one records where Action is set to "Set Priority to regular" per Ticket ID. If so, please try below suggestion. 

 

First, add a calculated column in Table1.

Index =
RANKX (
    FILTER ( 'Table1', Table1[TicketID] = EARLIER ( Table1[TicketID] ) ),
    Table1[Action date/time],
    ,
    ASC,
    DENSE
)

Then, add measure [Average] into a card visual.

Timediff =
VAR _CurrentActiontime =
    SELECTEDVALUE ( Table1[Action date/time] )
VAR _NextActiontime =
    CALCULATE (
        SELECTEDVALUE ( Table1[Action date/time] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[TicketID] ),
            Table1[Index]
                = MAX ( Table1[Index] ) + 1
        )
    )
VAR diff =
    IF (
        SELECTEDVALUE ( Table1[Action] ) = "Set priority to regular",
        DATEDIFF ( _CurrentActiontime, _NextActiontime, SECOND ),
        BLANK ()
    )
RETURN
    diff

Average =
AVERAGEX ( ALLSELECTED ( Table1 ), [Timediff] )

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-yulgu-msft this does the trick! Thank you very much for your help!

 

@AlB also many thanks for all your help!

AlB
Super User
Super User

Hi @Anonymous

Can you post sample data or share the pbix? It'd be easier to help out like that

Anonymous
Not applicable

Sure, this is the sample data. I hope this helps, thank you for your help.
TicketID Ticket created Action Action date/time
1521117-1-2019 15:03:09Ticket created7-1-2019 15:03:09
1521117-1-2019 15:03:09Set priority to regular7-1-2019 15:25:00
1521117-1-2019 15:03:09Give solution to customer7-1-2019 16:01:03
1521117-1-2019 15:03:09Ticket closed7-1-2019 16:02:01

@Anonymous

What do you exactly mean by 'giving priority to a ticket'? Is this when 'Action' is set to "Set priority to regular"?

Do you want to see the time between when 'Action' is set to "Set priority to regular" and whatever comes immediately afterwards for a specific TicketID?

Providing an example with your sample data would probably help clarify 

 

Anonymous
Not applicable

@AIB Yes i mean exactly what you are saying. With giving a priority to a ticket i indeed mean the action set priority to regular in the example table. And i indeed want the time between when 'Action' is set to "Set priority to regular" and whatever comes immediately afterwards that for a specific ticket ID. I want to give you all the sample data that you need, but i don't know what data you want extra besides the example table that i allready posted above.

@Anonymous

 

And how would the average have to be calculated exactly? Is it the average of that period of time you just described across all TicketIDs?

 

Anonymous
Not applicable

Yes exactly, something like this: - Ticket A took 5 minutes between the set priority and following action - Ticket B took 3 minutes between the set priority and following action - Ticket C took 15 minutes between the set priority and following action The average has to be the average of 5, 3 and 15 minutes Many thanks for your help.

@Anonymous

 

Try this measure in a card visual for instance:

 

AvgTimeCreation2Priority =
VAR _AuxTable =
    SUMMARIZECOLUMNS (
        Table1[TicketID];
        "Time2Next";
        VAR _CreationTime =
            VALUES ( Table1[ Ticket created] )
        VAR _NextActiontime =
            FIRSTNONBLANK (
                CALCULATETABLE (
                    VALUES ( Table1[ Action date/time] );
                    Table1[ Action date/time] > _CreationTime
                );
                1
            )
        VAR _TimeDiffMins = ( _NextActiontime - _CreationTime )* 24 * 60
        RETURN
            _TimeDiffMins
    )
RETURN
    AVERAGEX ( _AuxTable; [Time2Next] )

where Table1 is the name of your table. Do note that the result is in minutes (ex.  2 mins 30 seconds will show as 2,5). You can convert that into mins:secs format if you so need.

 

Anonymous
Not applicable

This looks great and when i use it on a card visual i get data. The only doubt i have is, does this use the rows with "set priority to" as start date/time? Maybe it's my beginner knowledge of PowerBI but i don't understand how this measure does that.

@Anonymous

 

Sorry, you're right. I misread and was taking the creation time as initial time. This is the updated version. As you see you can use this pattern with minor variations to extract the time period you are interested in.

 

AvgTimeSetPriority2Next =
VAR _AuxTable =
    SUMMARIZECOLUMNS (
        Table1[TicketID];
        "Time2Next";
        VAR _SetPriorityTime =
            CALCULATE (
                VALUES ( Table1[ Action date/time] );
                Table1[Action] = "Set priority to regular"
            )
        VAR _NextActiontime =
            FIRSTNONBLANK (
                CALCULATETABLE (
                    VALUES ( Table1[ Action date/time] );
                    Table1[ Action date/time] > _SetPriorityTime
                );
                1
            )
        VAR _TimeDiffMins = ( _NextActiontime - _SetPriorityTime ) * 24 * 60
        RETURN
            _TimeDiffMins
    )
RETURN
    AVERAGEX ( _AuxTable; [Time2Next] )
Anonymous
Not applicable

Thank you for al your kind help, i really appreciate it. I have tried the updated version but now receive the error "A table of multiple values was supplied where a single value was expected" Do you have any idea what causes this?

@Anonymous

 

Hmmm... I ran a quick test and it's working fine. Maybe you have more than one row for a TicketID with Action "Set priority to regular"?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.