undefined

[Anonymous]

I have this hierarchical table of employees that I have created a report to the line managers. But I want to restrict the level of granularity a manager should see. For example, my data has 8 levels of hierarchy and I want the managers to be able to see data for not more than 2 levels below them in the hierarchy. That is, no matter what level the manager is he should be able to see the data of his own report, the report of the first person , say "A" below him and the report of the next person, say "B" who is below "A". He should not see any report lower than that level. Please, I need your help. I have already created a filter to filter that each manager can see everyone below them usint their USERPRINCIPALNAME().

[V-lianl-msft · Accepted solution]

Hi @Anonymous  ,

Create an unrelated Employee_ID table as slicer.

Table 2 = DISTINCT('Table'[Employee_ID])

Use the path function to find the entire path from one member in original table and create Path length column.

Path = PATH('Table'[Employee_ID],'Table'[Manager's Employee_ID])
Path Length = PATHLENGTH('Table'[Path])

Create a measure to determine whether it is the longest path and apply it in visual level filter.

Measure = IF(SEARCH(SELECTEDVALUE('Table 2'[Employee_ID]),MAX('Table'[Path]),1,0)>0&&MAX('Table'[Path Length])=CALCULATE(MAX('Table'[Path Length]),ALL('Table')),1,0)

Refer to the:

Sample .pbix

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

[Greg_Deckler]

@Anonymous See if this helps: https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Hierarchical-Row-Level-Security/m-p/889567#M406

[amitchandak]

@Anonymous , See if these documents around RLS for organizational hierarchy can help

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies

[Anonymous]

nks Amit, this is exactly what guided me and all is working well apart from the fact that I'm not able to limit the view of the managers to view only two steps downwards below them. This means that the CEO will be seeing information for 7 stages below him which is not what I want. How do I limit this in the DAX below for example?

PATHCONTAINS(CurrentEmployees[Current_IDpath],

MAXX(FILTER(CurrentEmployees,

[Manager_Email_Address] = USERPRINCIPALNAME()),
CurrentEmployees[Staff_Number))