Utilising Relationships Across Multiple Levels of Hierarchy

[BInovice111]

Hi, 

I am a novice Power BI user who is using Power BI to create some visualisations of work that is being managed in Azure DevOps (Agile process). I am running into an issue when trying to relate the values of fields across multiple levels of hierarchy. Specifically:

- I have a top-level work item type - an Epic - which contains data about the customer for the work (e.g. Team Name)

- I have a mid-level work item type - Features - for features of the delivery

- I have a bottom-level work item type - Task - which tracks the effort involved in delivery

I am able to create visuals for my customer data e.g. number of Epics per customer, but I am running into issues when trying to related the effort fields in the Tasks up to the customer-level data on the Epics. Simply using e.g. Sum of "Completed Work" from my Task data is fine, but when I filter it to e.g. Customer A from the Epic work item, not all of the data is being counted.

The Task work items are ultimately related to the Epic work items through their work item linkages, but they may sometimes be direct child work items of the Epic or be child work items of the Feature which is itself a child of the Epic.

How do I make Power BI understand when the Tasks are related to the Epics across multiple levels of hierarhcy? Or am I missing something entirely in how to connect these pieces of data?

I am effectively looking to be able to calculate "IF Epic customer = X, THEN use all Tasks related to Epics where customer = X" for a visual.

[Ashish_Excel · Accepted solution]

Hi,

You may either have to unpivot your data in Power Query or use the USERELATIONSHIP() function with in a measure.  To receive specific help, share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[V-yubandi-msft]

Hi @BInovice111 ,

Following up to check whether you got a chance to review the suggestion given.If it helps,consider accepting it as solution,it will be helpful for other members of the community who have similar problems as yours to solve it faster. Glad to help. 

Thank You.

[V-yubandi-msft]

Hi @BInovice111 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[V-yubandi-msft]

Hi @BInovice111 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

 

Thank you.

[V-yubandi-msft · Accepted solution]

Hi @BInovice111 ,

Thank you for engaging with the Microsoft Fabric Community. By default, Power BI doesn’t automatically recognize the parent child relationships between Epics, Features, and Tasks. We need to manually establish those connections to make the data work properly.

FYI:

For More Detials:  Power BI integration and supported connections methods - Azure DevOps | Microsoft Learn

As @Ashish_Excel  mentioned, while USERELATIONSHIP() and unpivoting can be useful in certain scenarios, they don’t fully resolve this challenge. Since we’re linking Task level effort data to Epic level details like Customer, the main complexity lies in managing the hierarchical structure of Azure DevOps work items.

Thanks for your insights, @Ashish_Excel .

If my response solved your query, please mark it as the Accepted solution to help others find it easily. And if my answer was helpful, I'd really appreciate a 'Kudos'.

[Ashish_Excel · Accepted solution]

Hi,

You may either have to unpivot your data in Power Query or use the USERELATIONSHIP() function with in a measure.  To receive specific help, share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.