cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Proviten
New Member

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. 

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

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.

 

View solution in original post

2 REPLIES 2
v-chenwuz-msft
Community Support
Community Support

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.

 

View solution in original post

lbendlin
Super User
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 
    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".

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors