Inherited Value in a Parent Child Pattern

In the previous post in this series we created a Parent Child hierarchy using a Dax Pattern from SQLBI. There are times it is useful to calculate an inherited value from parent item to child. The assigned to person on a User Story to be passed onto the child tasks that are not assigned to anyone, the target date of a feature to be passed down to the child user story and then onto the child tasks. The child items need to be completed before the parent target date.

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

The Logic

We are going to add this as a calculated column into the work items table. If you were doing this by hand you would go through the items in the path column in reverse, looking up each target date until you found a non-blank one. In DAX we do this by creating a table of the WorkItemIDs in the path and looking up their target dates. Then we filter to only rows with target dates and take the first one.

Inherited Value DAX

Lets work through an example.

WorkItem 46 has an ItemPath of 38|39|45|46, which means it has a Path length of 4. So we start by using GENERATESERIES to create a column 1-4

GENERATESERIES(  1 , PATHLENGTH( WorkItems[ItemPath] ), 1 )

We put that inside a GENERATE function and use PATHITEMREVERSE to get the work item ids from the path and then LOOKUP to use that WorkItemID to get the TargetDate of each item. GENERATE function and nested VARs is one of my favourite combinations.

VAR tabWorkItems = 
    GENERATE(
        GENERATESERIES(  1 , PATHLENGTH( WorkItems[ItemPath] ), 1 ),
        VAR tableRow = [Value]
        VAR _WorkItemID = VALUE( PATHITEMREVERSE( WorkItems[ItemPath] , tableRow ) )
        VAR _TargetDate = LOOKUPVALUE( WorkItems[TargetDate] , WorkItems[WorkItemId] , _WorkItemID )
        RETURN ROW ( 
            "WorkItemID",_WorkItemID,
            "TargetDate",_TargetDate
        )
    )

Then we filter the table to rows with a TargetDate and take the first row ordered by Value. This gives us one row of data.

VAR tabFiltered =
    TOPN( 
        1,
        FILTER(tabWorkItems,[TargetDate]>BLANK()),
        [Value],ASC
    )

Finally we use MINX to extract the TargetDate of that single row. The final calculated column DAX looks like this.

Inherited Target Date = 
// Create a table of Value, WorkitemID and TargetDate
VAR tabWorkItems = 
    GENERATE(
        GENERATESERIES(  1 , PATHLENGTH( WorkItems[ItemPath] ), 1 ),
        VAR tableRow = [Value]
        VAR _WorkItemID = VALUE( PATHITEMREVERSE( WorkItems[ItemPath] , tableRow ) )
        VAR _TargetDate = LOOKUPVALUE( WorkItems[TargetDate] , WorkItems[WorkItemId] , _WorkItemID )
        RETURN ROW ( 
            "WorkItemID",_WorkItemID,
            "TargetDate",_TargetDate
        )
    )
// Take the first row of the filtered to non-blank
VAR tabFiltered =
    TOPN( 
        1,
        FILTER(tabWorkItems,[TargetDate]>BLANK()),
        [Value],ASC
    )
// Return the Target Date
VAR Result = MINX( tabFiltered , [TargetDate] )
RETURN Result

Inherited Value Conclusion

The Parent Child pattern is very useful and adding in the inherited values adds extra possibilities. I wanted to be able to identify the child items that were making the parent items late.

References

This post builds on the last post in this series and using the SQLBI Parent Child found at https://www.daxpatterns.com/parent-child-hierarchies/.