Search for a value in another row

[Proviten]

Hi! Im trying to deal with JIRA analysis. I'v got table like this:

Task	Type	Is epic	Relates to	Relates to IDEA
DM-1994	Simple task	0	DM-1823	(???)
DM-1925	Simple task	0	DM-1822	(???)
DM-1823	Epic	1	IDEA-80	1
DM-1822	Epic	1	-	0
IDEA-79	Idea	0	-	-
IDEA-80	Idea	0	-	-

There are three types of tasks. 

1) Simple task that could relates to epic. 

2) Epic that could be relative to idea.

3) Idea. 

How can I understand if a simple task is related to any idea?

For example in this case Simple task DM-1994 relates to Epic DM-1823 that relates to IDEA-80. In column 'Relates to IDEA' shoud be "1". 

Second task DM-1925 relates to Epic DM-1822, but this epic relates no idea. 

How can I fill the column 'Relates to IDEA' with a formula? 

Thanks. 

[v-chenwuz-msft · Accepted solution]

Hi @Proviten ,

DAX to create a new column.

Add =
VAR _Simpletask =
    CALCULATE(
        MAX( table[Relates to] ),
        FILTER( ALL( table ), [Task] = EARLIER( table[Relates to] ) )
    )
VAR _Epic =
    IF( [Relates to] = "-", 0, 1 )
RETURN
    SWITCH(
        TRUE(),
        TABLE[Type] = "Simple task", _Simpletask,
        TABLE[Type] = "Epic", _Epic,
        TABLE[Type] = "Idea", "-"
    )

Best Regards

Community Support Team _ chenwu zhu

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

[lbendlin]

Here is one possible implementation

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvHVNbS0NFHSUQrOzC3ISVUoSSzOBvJA4hZGxkqxOlA1RqY41BjB1YDU6yi5FmQmAylPF1dHXQsDJEkjhCRYFKzC3BKkNiU1EUUUqA9JNBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, Type = _t, #"Relates to" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Relates to IDEA", each try if Text.Start([Relates to],4)="IDEA" then 1 
    else 
    Table.RowCount(Table.SelectRows(Source,(n)=>n[Task]=Table.SelectRows(Source,(k)=>k[Task]=[Relates to])[Relates to]{0}))
  otherwise 0)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".