cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Re: Find time between one fixed row and one variable row

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?
Super User III
Super User III

Re: Find time between one fixed row and one variable row

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

Microsoft v-yulgu-msft
Microsoft

Re: Find time between one fixed row and one variable row

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

Anonymous
Not applicable

Re: Find time between one fixed row and one variable row

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

 

@AlB also many thanks for all your help!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors