Power BI & DAX: How to Flatten a Parent Child Hierarchy скачать в хорошем качестве

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 & ")" )