Forum Discussion

RichCoutinho's avatar
RichCoutinho
Frequent Visitor
2 years ago
Solved

PATH hierarchy

Hi all, 

 

I have in powerBI 2 tables: 

- Employee Data List which conatins all info about employees 

- Manager Data List, which contains the name and HRcode of employees who are managers 

and they are not interfaced/connected. 

 

I have create in PowerBi in the Employee Data List table the the hierachy of the company using the DAX :

 

Hierachy path = PATH('Employe Data List'[HRcode],'Employe Data List'[Manager HRcode])

 

 

which generated me a column Hierachy path with data like : 

1
1|2
1|4
1|4|11
1|5|10
1|6|9
1|5
1|6
1|3
1|2|7
1|8

 

I would like to be able to selected the name of the manager in a slicer (the data would come from the table Manager Data List) and the able to see all employees below this manager hierachy ( direct or indirect). 

 

Example:
1. Selecting Mark whihc is the CEO which has HRcode as 1, the table would repflect the name of all employees, mark inclusive.  

2. selecting ISabel which is a Manager which HRCode 2 , the bale would show All Isabel's direct reporting and indirect reports. 

 

 

I have tried to build a measure to achive it and so far i have parcially get there. 

 

I have tried first with this measure: 

 

_Filter hierarchy = 
IFERROR(  
    SEARCH( SELECTEDVALUE('Manager Data List'[HRcode]),
            SELECTEDVALUE('Employe Data List'[Hierachy path])),-1 
        )

 

and i have added this measure on the filter pane and selected is not -1. 

 

So when i Have only 1 manager selected it returns me the list of all employees (direct and indirect) under the selected manager however if i select 2 manager it doesnt work, it brings all employees. 

 

So I also have tired with this measure: 

 

zzzMeasure = 
PATHCONTAINS( SELECTEDVALUE('Employe Data List'[Hierachy path]) , SELECTEDVALUE('Manager Data List'[HRcode]))

 

withi this measure i was not able to add it as a filter, it doesnt open the option to select TRUE, to show only the true cases, so i have added in the table and it brings me the true or false but for all employees, and it i select more than 1 manager it brings false. 

 

so what i want to able to do is to select 1 or more manager and be able to see all employees (direct or inderect) under the hierarchy inclusding the managers in the employees list, example: 

 

Selecting Manager A wich has 2 direct reports and the direct reports SubManA1 has 3 reports and SubManA2 has 4 reports, and ManagerB , which has 3 Direct reports, and SubManB1 has no serports, SubMAnB2, has 1 report and BusManB3 has 5 reports. So my table should show: 
ManagerA,   

SubManA1,   

SubManA1_report1, 

 SubManA1_report2,     

SubManA1_report3 ,

SUBManA2, 

SubManA2_report1, 

SubManA2_report2,

SubManA2_report3

SubManA2_report4

ManagerB,

SUBManB1, 

SUBManB2,

SUBManB2_reports1

SUBManB3, 

 SUBManB3_reports1

SUBManB3_reports2

SUBManB3_reports3

SUBManB3_reports4

SUBManB3_reports5

 

and when i exctract this table to an Excel it would reflect the table generated on the PBI visual. 

 

Any help please ? 

Need Help
  • RichCoutinho's avatar
    RichCoutinho
    2 years ago

    Hi Ibendlin,

    This solution was not feasible for my client requirment. 

     

    After sleep on it, i have dicided to change the data model, so I have created: 

    1. Table 1: Employee data which has theemployee's info

    2. Table 2: Manager data which has the managers' info 

    3. Table 3: Reference table, which coained all HR code for employees, the HR code for the manager and i have created additional columns in Powequery adding the manger of the manager until the last level of managment in this case as 12 columns as we have 11 levels of hierarchy (counting with the employee). 

     

    Wiuth the table 3 in power query i have pivoted the columns into lines and filterd out the blanks cases of hierachy. 

     

    So in power BI I have used the HR code as primary key to connect Table 1: employee with the Table 3 Reference and as foreign key Manager HR code to conect Table 3 with Table 2 Manager's data. 

     

    So then with visuals i have added the managers name in the slicer giving option to search, and a table with employee info, and when i select the name of manager on the slicer it shows all employees (direct and indirect) under the maagers responsability. 

6 Replies