Fetch value from child work item

[PaulinoV]

Hey community, first time poster here!

I'm extremely new to the Power BI experience and would like some help with this issue.

In my data I have 3 Features that are children of an Epic. Those Features have a target date, what I need to do is a Donut Chart presenting which Epics "current" and which ones are "past due", as of right now, I can summon the donut chart with the amount of Features in "Current" or "Past Due".

But instead of showing 12 Features in Past due and 5 in Current, I would like it to show X Epics are in past due, refering to the target date of any of the 3 children.

Apologies if I wasn't able to explain myself very consicely and thanks in advance 

[sevenhills]

Yours is little complicated. I guess you are looking for rollup count based on a child calculated column.

I tried to understand and mimic your ask as below

• Let us say, orders as epics and order items are Features
• Assume each order item is shipped individually and has milestone statuses => each feature has its own checkpoints.
• Each order item goes through each status like ready to ship, postage prepared, shipped, on the way to delivery, delivered. - - > Feature has similar checkpoints.
• At order item level, You are rebucketing/recategorizing the order item status as started, in-process, delivered. - - > feature checkpoints to Past Due calculation.
• Rollup: Now, you want to status to check at each order level ~ i.e., based on all order items, you would like to know whether the order is delivered, in-progress based on all order items. - - > Similar approach you are doing for Epics by comparing each Features. I guess you are deciding based on any of the child items status i.e., past due calculation.
• I will say that you give weight to the past due calculation values and find a max value of all child items. I am suggesting weight at each item level, which is intermediate step, to know the overall status priority at parent level.
• Based on it you max of child items, assign the status to the parent i,.e. remapping at parent level.

Not sure, my explanation will help, thought of writing my view points. Hope it helps.

[v-yalanwu-msft]

Hi, @PaulinoV ;

I carefully studied the data you provided. Wow, this is a complex diagram. I also understand that you want to be able to calculate the parent count, but specifically which field in epics. I looked at each checkpoint belongs to an epic. Is the count always 1? In addition, your table relationship is a little strange (the relationship between epics and features is connected by parentworkitemid and workitemid?)

and What results do you want to output in the data you provide? And its logic？

Best Regards,
Community Support Team_ Yalan Wu

[PaulinoV]

Please find the dummy data example here

https://we.tl/t-7I2ENBHjda

[amitchandak]

@PaulinoV , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

may be like this

measure  =

countx(filter( values(Table[Feature]), not(isblank([Past due])) ), [Feature])

where Past due is a measure and you use only measures in pie no legend 

[PaulinoV]

Hi @amitchandak thanks for the prompt response!

I will try to better describe my scenario, in my project we have a constant structure

• Project Name (EPIC)
  • Checkpoint 1 (Feature)
  • Checkpoint 2(Feature)
  • Checkpoint 3 (Feature)

Each of those checkpoints has a Start Date and a Target Date, what I'm looking for is a donut chart that whenever one of the 3 checkpoints is "past due" shows that one Epic is late (regardless if it's checkpoint 1,2,3), as of right now it just counts the number of checkpoints that are late (several checkpoints can be delayed in one epic).

I created a column in the Features table to keep track of the past due checkpoints:

Past Due = if(DATEDIFF(Features[TargetDate],TODAY(),DAY)<0,"Current", if(DATEDIFF(Features[TargetDate],TODAY(),DAY)>=0 AND Feature[State] <> Closed, "Past Due"))

As seen in this image below

I would like it to count 1 epic, regardless if its one, two or three checkpoints delayed, same for "current" or "on time" status.

Short story I would like that every time that one checkpoint is late it flags, counts the parent epic.

(i'm trying to get a file with sensible data removed)

Hopefully I was able to explain myself better 🙂 

Thanks in advance for your help!