Forum Discussion

Proviten's avatar
Proviten
New Member
4 years ago
Solved

Search for a value in another row

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

TaskTypeIs epicRelates toRelates to IDEA
DM-1994Simple task0DM-1823(???)
DM-1925Simple task0DM-1822(???)
DM-1823Epic1IDEA-801
DM-1822Epic1-0
IDEA-79Idea0--
IDEA-80Idea0--

 

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. 

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

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

     

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.