RLS for an Org hierarchy

Hello

I am getting myslef familiarized with the Power BI RLS functionality. I need to develop a dynamic security set up for the model being built. The ask is to have the report be viewed by only the Managers or superiors who are part of the hierarchy.
My model has a shipment table with managers responsible. These managers have superiors as SVP’s and their superiors as Presidents. The shipments data will have the President’s ID’s, but in the future could have the SVP id as person responsible for the shipment to the customer.

President P1 - Can view all the data under his line of business
SVP1 - SHould view details of shipments of all managers under his Group

I have this hierarchy in a table -USers_hier.

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

Attaching my model here and looking for some advice.

Hi @train, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

I had looked at an example posted in the below link.

I have Level 1 users which are not part of the FACT table. But i can utilize the user hierarchy table to derive the path of Level1 -> Level2 -> Level3. The Level 1 users will have visibility for all the shipments irrespective of managers.
Level1 - Presidents - 4 members - All access
Level 2 - All Divisional VP’s
Level 3 - Managers
I wanted to keep the security structure simple so that it is easily scaleable and not be a maint nightmare.

Would like to learn if there is a simple approach rather than creating a bunch of roles. Please advice.

Hi Train,

PFA solution for your requirement.

Step1 - Created RLS Users Table. Attached screen shot
Step2 - Created role for shipment table on Manager column. Below is the logic for RLS

IF (
USERPRINCIPALNAME () IN VALUES ( ‘RLS Users’[Email] ),
[Manager]
IN SELECTCOLUMNS (
FILTER ( ‘RLS Users’, ‘RLS Users’[Email] = USERPRINCIPALNAME () ),
“Managers”, ‘RLS Users’[Manager]
),
[Manager] IN VALUES ( Shipment[Manager] )
)

Step3 - Publish the report to Power BI service and assign the role to Mangers, SVP’s and Presidents.

Let me know if you’ve any questions.

RLS Users

1 Like

Thanks @Rajesh. Appreciate your guidance here.

That was a good approach. But i can see that it works with RM1 & SVP1.
The P1 which are the executive levels are the ones that i am struggling.
Since the executives will need to view all the shipments, is it better to avoid the security for that layer and add the rest of the hierarchy in to the RLS Users table as suggested ?
Attaching the model with the RLS added .

https://drive.google.com/file/d/1t3I8X3LLI8uqhNgELnr1HiAi7e3krJkB/view?usp=sharing

@train This works for P1 as well. I didn’t added their email id’s in the RLS Users table so If condition executes the else part. They can see all the shipments.

Just add P1 to the role in Power BI Service.

Thanks @Rajesh

Ok. My bad. I tried that and worked fine. Basically that means anyone on Level 1 need not be in the RLS Users.
I will play with it to see the scalability if we expand the hierarchy to more levels. Thanks for the help again.

@train Yes. In this approach Level 1(Who wants to see all the data) should not be in the RLS Users Table.

1 Like