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

[Anonymous]

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 !

Id	Issue Id	Transitionned	From Status	To Status
178244	26496	2017-01-13 9:41:00 AM	Open	In Analyse
178245	26496	2017-01-13 9:41:00 AM	In Analyse	To Do
215395	26496	2017-05-18 1:57:00 PM	To Do	Done
216558	26496	2017-05-24 1:38:00 PM	Done	Validation
216691	26496	2017-05-24 4:20:00 PM	Validation	Done
180005	26566	2017-01-20 9:46:00 AM	Open	In Analyse
180006	26566	2017-01-20 9:47:00 AM	In Analyse	To Do
202703	26566	2017-04-06 2:31:00 PM	To Do	Validation
202706	26566	2017-04-06 2:33:00 PM	Validation	To Do
209420	26566	2017-04-28 4:38:00 PM	To Do	In Progress
209421	26566	2017-04-28 4:38:00 PM	In Progress	Validation
209444	26566	2017-04-28 4:48:00 PM	Validation	In Progress
211150	26566	2017-05-04 3:53:00 PM	In Progress	Done
211151	26566	2017-05-04 3:53:00 PM	Done	Validation
213229	26566	2017-05-10 5:09:00 PM	Validation	Done

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

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 )

[Ashish_Mathur]

Hi @Anonymous,

Please show your expected result.

[Anonymous]

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.

[Anonymous]

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 

[Zubair_Muhammad]

HI @Anonymous

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 )

[Aimeeclaird]

Hi @Zubair_Muhammad - apologies, appreciate your original response was a few years ago now but I wondered if you could help me please. 

I am trying to find a way to 'look up' a value in a different row of the table based on criteria. I've attached an example file which is a similar structure to my data. 

Dogs and Cats example data.xlsx

I am trying to write something in a customer/calculated column along the lines of:

if [Sale Type] = "Dog" then FIND [ID] = [ID] and [Sale Type] = "Cat" then return the [Date of Sale] however I'm not sure of the correct way to write this in DAX. 

Any help would be appreciated as your suggestion on this thread seems relevant to my problem, however i'm struggling to adapt it. 

[Zubair_Muhammad]

@Anonymous

Hopefully, you won't need the ID now

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

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 )

[Anonymous]

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

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

See the atached file

With your sample data