Help with Custom Column Lookup

[Meraptan]

Hello - I'm trying to create a custom column to do a few things, but can't figure out a formula to accomplish it.  The data is from a Tasks table in Project Online, so the rows are 'Tasks'.  I'm trying to end up with a 'My Date' custom column that has values like this:

Task Type	Parent ID	Task ID	Start Date	My Date
1	AB	CD	5/1/2019
2	AB	GH	6/1/2019
3	IJ	AB	7/1/2019	5/1/2019
2	BB	HH	6/1/2019
3	KK	BB	4/1/2019	3/1/2019
1	BB	DD	3/1/2019

The Logic I'm trying to use is something like this:  My Date= For all rows that are Task Type = 3, return the Start Date of the Task Type = 1 row that has the same Parent ID as the Task ID of the Task Type = 3 row, otherwise Blank

These are actual parent/child relationships and I was looking for Path and Lookup functions that can match columns, etc., but no success.  As you can guess, all Task Type = 3 rows are the Parent tasks of the others. I need someone smarter than me to tackle it please.   I know - kind of weird, but it does have a purpose.   Thanks a million.

[v-xuding-msft · Accepted solution]

Hi @Meraptan ,

I created a measure that you can have a try.

Measure1 = var d=CALCULATE(MAX('Table'[Task ID]),FILTER('Table','Table'[Task Type]=3))
return
IF(MAX('Table'[Task Type])=3,CALCULATE(MAX('Table'[Start Date]),FILTER(ALLSELECTED('Table'),'Table'[Task Type]=1&&'Table'[Parent ID]=d)),BLANK())

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.