cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jacob_
Frequent Visitor

RLS based on many columns

hello there,

 

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

Jacob__0-1619539511731.png

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!

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

Nerdy_Julie
Regular Visitor

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors