Forum Discussion
Recursive Hierarchy; Managers and Employees
Hello PowerBI Community!
I am trying to accomplish an organizational hierarchy report based on a table Managers and Users. Sample data below:
As you can see, every employee should have a managerID associated to them.
The best visualization will most likely come from a Matrix or a Hierarchy Slicer, but it seems the dropdowns are not as recursive as I need them to be (i.e. Manager456->Manager123 should have the users of Manager123):
I assume I will need to write a query to build this heirarchy, but I do not know how many levels up/down I will have, so adding Paths/columns will not work here. Any anecdotes, examples or pointers are much appreciated.
Thanks,
PrintDream
Hi PrintDream ,
1. Here is a blog talking about similar issue, please check if it helps.
Power BI Tips & Tricks: Filter by Manager Hierarchy
2.Or you may try merge:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jU0MlbSUQoFUbE6WPgmpmZAvi8aP9TcwlIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ManagerID = _t, UserID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ManagerID", type text}, {"UserID", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"UserID"}, #"Changed Type", {"ManagerID"}, "Changed Type", JoinKind.LeftOuter), #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"UserID"}, {"Sub.UserID"}) in #"Expanded Changed Type"More threads:
Solved: Manager Level Hierarchies with multiples in Power ... - Microsoft Power BI Community
How to create end to end Hierarchy Column in Power BI to show Parent Child Relationship - YouTube
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- lbendlin
Super User
Familiarize yourself with the PATH functions. Also figure out if your hierarchy is well defined or (more likely) ragged.
Hi PrintDream ,
1. Here is a blog talking about similar issue, please check if it helps.
Power BI Tips & Tricks: Filter by Manager Hierarchy
2.Or you may try merge:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jU0MlbSUQoFUbE6WPgmpmZAvi8aP9TcwlIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ManagerID = _t, UserID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ManagerID", type text}, {"UserID", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"UserID"}, #"Changed Type", {"ManagerID"}, "Changed Type", JoinKind.LeftOuter), #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"UserID"}, {"Sub.UserID"}) in #"Expanded Changed Type"More threads:
Solved: Manager Level Hierarchies with multiples in Power ... - Microsoft Power BI Community
How to create end to end Hierarchy Column in Power BI to show Parent Child Relationship - YouTube
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
