Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 t...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago
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?
AlB's avatar
AlB
Community Champion
7 years ago

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"?