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

Highlighted
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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors