Search code examples
sql-serverpowerbidax

How to filter using hierarchical relationships within Power BI

I have two columns one being employee and the other being direct manager. The direct managers also show up in the employee column along with their respective direct managers creating a sort of hierarchy. I want to be able to create a filter such that when a manager is selected the result will show a table with all employees that are under that manager not just directly under, but anywhere within the hierarchy.

employee direct_manager
1        6
2        3
3        1
4        7
5        4

This demonstrates the logic in that we have different layers of hierarchy but the table only shows the direct manager

Solution

  • In Tabular you need to solve this with modeling. Either flatten parent-child hierarchies into multiple tables. See https://www.daxpatterns.com/parent-child-hierarchies/

    Or, I think what you're after here, would be to materialize all the indirect "reports-to" relationship pairs in a table.