Looking up values in multiple columns on an Organization Hierarchy

[josemendoza]

I have the table below.

Employee	Manager	Manager+1	Manager+2	Manager+3	Manager+4	Manager+5
1	4	10	9	7	8	11
2	4	10	9	7	8	11
3	4	10	9	7	8	11
4	10	9	7	8	11
5	6	10	9	7	8	11
6	10	9	7	8	11
7	8	11
8	11
9	7	8	11
10	9	7	8	11

As you can see in this company there are 11 employees - the employe 11 is the CEO and he is the uppermost manager. Then each column shows how the hierarchy level of each manager in the organization.

I will have a specific manager (lets call him/her Manager X) accessing this dashboard. I would want Manager X to only see the Employees from his own hierarchy. 

For example:

If manager 11 accesses, I would like him to see all 10 rows, as he is the uppermost manager.

If manager 10 accesses, I would like him to see rows 1,2,3,5 and 6 only.

To do this, I created a new measure:

Check = IF(MAX('FullHierarchy'[Manager+0])=[Selected_Supervisor]||MAX('FullHierarchy'[Manager+1])=[Selected_Supervisor]||MAX('FullHierarchy'[Manager+2])=[Selected_Supervisor]||

MAX('FullHierarchy'[Manager+3])=[Selected_Supervisor]||MAX('FullHierarchy'[Manager+4])=[Selected_Supervisor]||MAX('FullHierarchy'[Manager+5])=[Selected_Supervisor]||MAX('FullHierarchy'[Manager+6])=[Selected_Supervisor],"Yes","No")

The intent of this measure is to find the value of the Manager accessing (selected supervisor) on ALL the manager´s columns, and if found in at least one, have a "Yes", so that I can later filter these "Yes" rows IN. 

Unfortunately this measure doesnt work. 

Any ideas? Thanks!

[v-yangliu-msft · Accepted solution]

Hi  @josemendoza ,

Here are the steps you can follow：

1. Copy the Table in Power Query to form Table2.

2. Check the columns with yellow labels – Tranform – Unpivot Columns.

Result：

3. Create measure.

Flag =
var _select=SELECTEDVALUE('Table2'[Value])
var _Flag=SELECTCOLUMNS(FILTER(ALL(Table2),'Table2'[Value]=_select),"1",[Employee])
return
IF(
    MAX('Table'[Employee]) in _Flag,1,0)

4. Place [Flag]in Filters, set is=1, apply filter.

5. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@josemendoza , refer if one of the two can help

https://radacad.com/parsing-organizational-hierarchy-or-chart-of-accounts-in-power-bi-with-parent-ch...

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/