Power BI & DAX: How to Flatten a 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 & ")"
)