DevOps Parent Child Hierarchy in Power BI
Azure DevOps is built around the pattern than items have child items and so a backlog view will show the items in a hierarchy. When the work items some into Power BI that is not shown. So this gives me the chance to use one of my favourite resources from SQLBI, DAX Patterns and their Parent-Child pattern.
I am using the Agile process so the hierarchy is Epic – Feature – User Story – Task. I have made sure my project includes all the layers correctly. You can work around oddities but for this post we will assume all layers of the items are as we expect.
Power BI and DevOps Series
This post is part of a series:
- Get DevOps Data into Power BI
- Add Parent Child Hierarchy using DAX Patterns
- Inherited Value in a Parent Child pattern
- Add conditional formatting icons the easy way
DAX Patterns – Parent Child Hierarchy
DAX patterns comes in multiple formats, a book, a website and videos. They are written by SQLBI and have saved me hours and hours. For this post we are going to adapt the Parent-Child pattern to DevOps data. I am going to use the report built in the previous post in this series. The pattern is fully explained here https://www.daxpatterns.com/parent-child-hierarchies
Adding the Path
The pattern uses a function called path. This function takes an row id and the parent row id and walks up the path until it reaches a row with no parent. Work Items in DevOps have WorkItemID and ParentWorkItemID. So following the instructions we add a column to the WorkItems table.
ItemPath = PATH(WorkItems[WorkItemId],WorkItems[ParentWorkItemId])
The ItemPath gives us the list of IDs in the hierarchy above the current row. It does requires the path to be complete so it might not work if you have filtered out some of the items.
Adding DevOps Hierarchy Level Columns
From the above post these are the Level 1 to Level 4 columns, we just need to swap out the right column names. We also know the the names of our levels from DevOps so we can use slightly more meaningful names, Level 1 = Epic, Level 2 = Feature etc. Add the 4 columns using the pattern below, copied straight from the SQLBI post.
Epic =
VAR LevelNumber = 1
VAR LevelKey = PATHITEM ( WorkItems[ItemPath], LevelNumber, INTEGER )
VAR LevelName = LOOKUPVALUE ( WorkItems[Title], WorkItems[WorkItemId], LevelKey )
VAR Result = LevelName
RETURN
Result
Adding a Matrix
The quickest way to see the hierarchy in action is to add a matrix visual. Into the rows we put the levels of the hierarchy to whatever levels we want. Into the values I put # Work Items. It looks great we can expand the different levels and see the 72 work items split out into features etc.
But we get blank rows at the top of each section. Not to worry of course our friends at SQLBI have a solution.
Fixing the measures
In order for this to work we need to compare the maximum path length with the level being displayed. The path length we add as a column Depth to Work Items and then add a measure MaxRowDepth. Then using ISINSCOPE function we calculate the display depth in a measure called ItemBrowseDepth.
Column Added to WorkItems table
Depth = PATHLENGTH( WorkItems[ItemPath] )2 Measures Added to WorkItems table
MaxRowDepth = MAX( WorkItems[Depth] )
ItemBrowseDepth =
ISINSCOPE ( WorkItems[Epic] )
+ ISINSCOPE ( WorkItems[Feature] )
+ ISINSCOPE ( WorkItems[User Story] )
+ ISINSCOPE ( WorkItems[Task] )Now we can add measures that use these values to decide if to display the row or not. The logic being we only show rows where the browse depth is less than or equal to the max row depth. So I fix the # Work Items using the example from the SQLBI Pattern.
# Work Items =
VAR Val = COUNTROWS ( WorkItems )
VAR ShowRow = [ItemBrowseDepth] <= [MaxRowDepth]
VAR Result = IF ( ShowRow, Val )
RETURN
ResultThis now fixes the matrix to not show blank rows
Conclusion on Parent Child Hierarchy
This makes the DevOps data easy to report on. The measures roll up the data to the different levels so the total hours can be calculated etc. Handling DevOps plans with missing layers is hard work, I’ve done it where some User Stories were children of the Epic. That made the pattern clunky so I’d probably push back now and insist the DevOps plan was kept clean.
More Power BI Posts
- Conditional Formatting Update
- Data Refresh Date
- Using Inactive Relationships in a Measure
- DAX CrossFilter Function
- COALESCE Function to Remove Blanks
- Personalize Visuals
- Gradient Legends
- Endorse a Dataset as Promoted or Certified
- Q&A Synonyms Update
- Import Text Using Examples
- Paginated Report Resources
- Refreshing Datasets Automatically with Power BI Dataflows
- Charticulator
- Dataverse Connector – July 2022 Update
- Dataverse Choice Columns
- Switch Dataverse Tenancy
- Connecting to Google Analytics
- Take Over a Dataset
- Export Data from Power BI Visuals
- Embed a Paginated Report
- Using SQL on Dataverse for Power BI
- Power Platform Solution and Power BI Series
- Creating a Custom Smart Narrative
- Power Automate Button in a Power BI Report
Power BI Series
