Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
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".