PATH hierarchy

[RichCoutinho]

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 ? 

[lbendlin]

Usually you only have one table, with all employees and their manager.  What made you use two tables?

[RichCoutinho]

On the Employee Table i have the the managers data as else, with this info i was able to make the Path. 

I have create a second table with manager only to try to achieve what i was doing, but if there's a way to have it with only employee table which contains the managers data i'd be very happy as well. 

Because when i tried to use the manager data on the slicer it was giving me only the direct reports, and i want direct and inderects. 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[RichCoutinho]

Hi ,

Here is a PBI sample of the data. 

It's formed by 2 Table: 

1. Employee Data List : whihc has names and data of all employees 

2. Manager List: Whihc has the name of only Managers to be used in the slicer. 

I want to be able to Selected the name of themanager on the Slicer and in a Table visula present hame and information of all employees under the selected manager. 

I have created a Measure there called _filter hierarchy, which allowed me to achive that only when 1 manager is selected, if select 2 or manager's it soent work as expcted, it brings all employees and i want to present only employees who are below the selected managers. 

Here it works well when i select one manager

error when select 2 or more managers

I believe it happens because Seelctevalue function bring only one result of selection. So I am wondering if ther's a way to achive it: indenpendent of number of managers selected to show all employees (direct and indirect reports) below the seleted managers. 

https://1drv.ms/u/s!AriLPDMeppqUkD_wo4HHCbg1iVJ6?e=nqT7qk 

[lbendlin]

Here's an alternative approach that doesn't need the Manager table.

There are other hierarchy slicers that suppress blanks in ragged hierarchies.

[RichCoutinho · Accepted solution]

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.