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

This post is part of a series

• Getting DevOps Data into Power BI
• Adding Parent Child Hierarchy using DAX Patterns
• Inheriting Values in the Parent Child pattern
• Adding 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
    Result

This 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.