POWER BI & DAX: How to Flatten Parent Child Hierarchy

Unfortunately, Power BI does not natively support Parent/Child hierarchies. Luckily, we have several DAX functions that make it possible to convert (or flatten) a parent/child hierarchy into a traditional Power BI Level based hierarchy.

In this video I introduce parent child hierarchies, talk about some of the requirements necessary for Power BI to be able to work these hierarchies and then I walk through all the steps necessary to use a parent child hierarchy in Power BI.

These are the calculations I used in the video for your reference:

hasDirectReports =
CONTAINS (
    ALL ( Employee ),
    Employee[Manager Employee ID], Employee[EmployeeID]
)

path =
PATH ( Employee[EmployeeID], Employee[Manager Employee ID] )

Level 1 ID =
PATHITEM ( Employee[path], 1, INTEGER )

Level 2 ID =
PATHITEM ( Employee[path], 2, INTEGER )

Level 3 ID =
PATHITEM ( Employee[path], 3, INTEGER )

Level 4 ID =
PATHITEM ( Employee[path], 4, INTEGER )

Level 1 =
VAR ManagerName =
    CALCULATE (
        MAX ( Employee[Employee Name] ),
        FILTER (
            ALL ( Employee ),
            Employee[EmployeeID] = EARLIER ( Employee[Level 1 ID] )
        )
    )
VAR title =
    CALCULATE (
        MAX ( Employee[Title] ),
        FILTER (
            ALL ( Employee ),
            Employee[EmployeeID] = EARLIER ( Employee[Level 1 ID] )
        )
    )
RETURN
    IF (
        ManagerName = BLANK (),
        Employee[Employee Name],
        ManagerName & ” (“ & title & “)”
    )

Level 2 =
VAR ManagerName =
    CALCULATE (
        MAX ( Employee[Employee Name] ),
        FILTER (
            ALL ( Employee ),
            Employee[EmployeeID] = EARLIER ( Employee[Level 2 ID] )
        )
    )
VAR title =
    CALCULATE (
        MAX ( Employee[Title] ),
        FILTER (
            ALL ( Employee ),
            Employee[EmployeeID] = EARLIER ( Employee[Level 2 ID] )
        )
    )
RETURN
    IF (
        ManagerName = BLANK (),
        Employee[Employee Name],
        ManagerName & ” (“ & title & “)”
    )

Level 3 =
VAR ManagerName =
    CALCULATE (
        MAX ( Employee[Employee Name] ),
        FILTER (
            ALL ( Employee ),
            Employee[EmployeeID] = EARLIER ( Employee[Level 3 ID] )
        )
    )
VAR title =
    CALCULATE (
        MAX ( Employee[Title] ),
        FILTER (
            ALL ( Employee ),
            Employee[EmployeeID] = EARLIER ( Employee[Level 3 ID] )
        )
    )
RETURN
    IF (
        ManagerName = BLANK (),
        Employee[Employee Name],
        ManagerName & ” (“ & title & “)”
    )

Level 4 =
VAR ManagerName =
    CALCULATE (
        MAX ( Employee[Employee Name] ),
        FILTER (
            ALL ( Employee ),
            Employee[EmployeeID] = EARLIER ( Employee[Level 4 ID] )
        )
    )
VAR title =
    CALCULATE (
        MAX ( Employee[Title] ),
        FILTER (
            ALL ( Employee ),
            Employee[EmployeeID] = EARLIER ( Employee[Level 4 ID] )
        )
    )
RETURN
    IF (
        ManagerName = BLANK (),
        IF ( Employee[hasDirectReports], Employee[Employee Name], Employee[Level 3] ),
        ManagerName & ” (“ & title & “)”
    )