RLS with org hierarchy

[Pbi07]

Hello 

I need to develop a dynamic security set up for the data model being built and needed some help. 
My model has a shipment table with managers responsible. These managers have superiors as SVP’s and their superiors as Presidents. Shipments data will have the Manager or SVP's tied to shipment lines. But will not have the President’s ID’s.

The ask is to have the report be viewed by only the Managers or superiors who are part of the hierarchy.

President P1 - Can view all the data under his line of business. There are 4 president's who can view all the data. 
SVP1 - SHould view details of shipments of all managers under his Group. 

Managers can view only the data they are tied to in the shipments. 

I was looking at the below link , but in my scenario i could have multiple Level 1's, so the path gets distorted. 

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

Attaching the sample data that i am working with. 

I have this hierarchy in a table -USers_hier.

Can RLS be applied if the user is not part of the shipments data?

https://drive.google.com/file/d/14GfMN15z7irifqC9WqibZbySpWD-vGkd/view?usp=sharing

[Yogesh_Telasang]

Hi,

In this case you'll have to create 2 roles;

Role 1 - for managers/SVPs whose details are available in the shipment data. You can use pathcontains and username/userprincipalname to validate the user. Add managers and SVPs to this group in the RLS of dataset.

Role 2 - for Presidents whose details are unavilable in the shipment data. Just create a role without using security filter functions and add presidents to this group similarly.

This way Presidents have access to view all the data and managers/SVPs have access to view the data that they are entitled to.

[v-xicai]

Hi @Pbi07 ,

You may set Row-Level Security (RLS) to manage roles,  please see the link Power BI Desktop Dynamic security cheat sheet, which described the detailed steps. Maybe it doesn't work, there are some tips to let it work and test it efficiently.

Best Regards,

Amy 

Community Support Team _ Amy

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

[amitchandak]

@Pbi07 , refer if these can help

RLS - Row Level security
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns...
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
https://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/

[Pbi07]

Thanks @amitchandak 

These are good reads on RLS. 

I wanted to clarify 1 point. 

If there are users - Mainly executives who are not part of the FACT data and if they need access to all the reporting, what is the usual practice?  Is it better to NOT to add them to any roles? 

I liked the dynamic approach narrated by Reza here - https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi.  But, it kind of breaks if there are multiple managers for a user.  

[amitchandak · Accepted solution]

@Pbi07 , if their reportees is part of if they should get access to it.

I think the MBAS Gallary video they have even shown how to bypass it for a certain user. Saw it long back so not sure now.  Even you can have a fact where you do no apply RLS and give access to the executive from that

https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns...