RLS based on many columns

[Jacob_]

hello there,

let's say that I have two below tables (employee & related fact table):

keeping the structure of the model I need to create RLS role that allow me to filter data for each employee in following manner:

- employee1 and employee2 see only theirs data

- employee3 and employee4 see theirs data and subordinate employee1 and employee2 data

- employee5, employee6 and employee7 see theirs data and all data for employee1, employee2, employee3 and employee4

- and so one.. 

is there any simple solution for such a rls role?

Regards!

[v-jayw-msft · Accepted solution]

Hi @Jacob_ ,

Add a level column for employee and level role for RLS.

- employee1 and employee2 belong to level1, the column value =1.

- employee3 and employee4 belong to level2, the column value =2.

- employee5, employee6 and employee7 belong to level3, the column value =3.

The formula for RLS should be 'employee = employee || level < level'.

Best Regards,

Jay

[Nerdy_Julie]

Hi @Jacob_  I believe I have created something similar to what you are asking for.

I have a large group of employees that can only see their data.

A small group of managers that can only see their direct report’s data.

A medium size group of people that can see everything.

First step was creating an Excel file that lists all of the employees that are only allowed to see their own data.  This table included their name, territory, and email address (You could create this table within Power BI but in my case the file is maintained by another department, so I created it in Excel).  Then I created a role for these people with a filter applied to only show data where “ [Email Address] = userprincipalname() ”

For the managers,  I created a role for each manager and applied a filter where the territory column on the bridge table equals their territory.

For those that can see everything, I created a role that had no filters applied. This step is technically not required as anyone granted permission to the report will see everything unless they are in a RLS role,  but I wanted an easy way to see who had the “see all” role so I went this route.

Once the report was published, I added the appropriate employees to the new roles.

Hope this helps! Let me know if you have questions.