cancel
Showing results for
Did you mean:
New Member

## Search for a value in another row

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.

1 ACCEPTED SOLUTION
Community Support

Hi @Proviten ,

DAX to create a new column.

``````Add =
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] = "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.

2 REPLIES 2
Community Support

Hi @Proviten ,

DAX to create a new column.

``````Add =
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] = "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.

Super User

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
otherwise 0)
in

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

Announcements