cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adigau Frequent Visitor
Frequent Visitor

By row, search for a value in another row of the same table

Hello everybody, I'm new to Power BI but I've already done a lot of work by myself thanks to this forum.

I'm pretty sure the problem here is my understanding of DAX and some functions, so I hope you can help me.

 

 

For the context, I'm connected to my JIRA's company API and I have gathered all the status transitions in a table.

For each transition I know the source and the destination. What I want is to be able to calculate the duration of each step.

 

 

To do that, I need to get the elapsed time between the 'Transitionned' date of my current line and the 'Transitionned' date of the earlier transaction for this 'Issue Id'. 

 

I am not able to create a measure that can do that for me.  

You will find below an example for two issues of my table.

 

Thanks in advance !

 

 

IdIssue IdTransitionnedFrom StatusTo Status
178244264962017-01-13 9:41:00 AMOpenIn Analyse
178245264962017-01-13 9:41:00 AMIn AnalyseTo Do
215395264962017-05-18 1:57:00 PMTo DoDone
216558264962017-05-24 1:38:00 PMDoneValidation
216691264962017-05-24 4:20:00 PMValidationDone
180005265662017-01-20 9:46:00 AMOpenIn Analyse
180006265662017-01-20 9:47:00 AMIn AnalyseTo Do
202703265662017-04-06 2:31:00 PMTo DoValidation
202706265662017-04-06 2:33:00 PMValidationTo Do
209420265662017-04-28 4:38:00 PMTo DoIn Progress
209421265662017-04-28 4:38:00 PMIn ProgressValidation
209444265662017-04-28 4:48:00 PMValidationIn Progress
211150265662017-05-04 3:53:00 PMIn ProgressDone
211151265662017-05-04 3:53:00 PMDoneValidation
213229265662017-05-10 5:09:00 PMValidationDone
2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Re: By row, search for a value in another row of the same table

Hi @adigau

 

Try this MEASURE

 

Duration =
VAR PreviousStepTime =
    CALCULATE (
        MAX ( TableName[Transitionned] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Issue Id] ),
            TableName[Id] < SELECTEDVALUE ( TableName[Id] )
        )
    )
RETURN
    DATEDIFF ( PreviousStepTime, SELECTEDVALUE ( TableName[Transitionned] ), DAY )
Try my new Power BI game Cross the River

View solution in original post

Super User III
Super User III

Re: By row, search for a value in another row of the same table

Hi @adigau

 

See the atached file

With your sample data

 

217.png

 

 

 

Try my new Power BI game Cross the River

View solution in original post

8 REPLIES 8
Super User III
Super User III

Re: By row, search for a value in another row of the same table

Hi @adigau

 

Try this MEASURE

 

Duration =
VAR PreviousStepTime =
    CALCULATE (
        MAX ( TableName[Transitionned] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Issue Id] ),
            TableName[Id] < SELECTEDVALUE ( TableName[Id] )
        )
    )
RETURN
    DATEDIFF ( PreviousStepTime, SELECTEDVALUE ( TableName[Transitionned] ), DAY )
Try my new Power BI game Cross the River

View solution in original post

Super User III
Super User III

Re: By row, search for a value in another row of the same table

Hi @adigau

 

See the atached file

With your sample data

 

217.png

 

 

 

Try my new Power BI game Cross the River

View solution in original post

Super User IV
Super User IV

Re: By row, search for a value in another row of the same table

Hi @adigau,

 

Please show your expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
adigau Frequent Visitor
Frequent Visitor

Re: By row, search for a value in another row of the same table

Hey @Zubair_Muhammad, you measure is exactly what I needed, thank you so much! Very helpful!

adigau Frequent Visitor
Frequent Visitor

Re: By row, search for a value in another row of the same table

Hey @Ashish_Mathur, if you look at @Zubair_Muhammad solution, there is a pbix attached, his results are exactly what I was looking for, his measure returns the results I wanted.

 

Thanks for your help, next time I will add the expected results, I should have done it.

adigau Frequent Visitor
Frequent Visitor

Re: By row, search for a value in another row of the same table

Hey @Zubair_Muhammad, I found a problem in the use of your measure in my very special context. The problem is that if I don't display the Id column, the measure won't find the value and return nothing. I wanted to agregate the results to calculate the duration per status, or per issue or per issue type.

 

I know it was not in my orioginal request, that's why I created a new topic: My measure can only work if I display the fields it uses 

Super User III
Super User III

Re: By row, search for a value in another row of the same table

HI @adigau

 

Try adding a calculated column instead of a MEASURE

 

Duration_Column =
VAR PreviousStepTime =
    CALCULATE (
        MAX ( TableName[Transitionned] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Issue Id] ),
            TableName[Id] < EARLIER ( TableName[Id] )
        )
    )
RETURN
    DATEDIFF ( PreviousStepTime, TableName[Transitionned], DAY )
Try my new Power BI game Cross the River
Super User III
Super User III

Re: By row, search for a value in another row of the same table

@adigau

 

Hopefully, you won't need the ID now

 

901.png

Try my new Power BI game Cross the River

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.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors