cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
renanpinheiro Helper I
Helper I

Dynamic RLS

Hi guys.
I need to create a dynamic RLS as follows:
I have a training table, which has an instructor column. I need to share the panels with the instructors, but only the training data they gave.

Who can help me?

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Re: Dynamic RLS

@renanpinheiro,

Add another email address column for the instructors in your table, then use USERNAME() function to implement dynamic row level security as described in the following similar blog.

http://radacad.com/dynamic-row-level-security-with-power-bi-made-simple

Regards,
Lydia

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

View solution in original post

7 REPLIES 7
Microsoft
Microsoft

Re: Dynamic RLS

@renanpinheiro,

Add another email address column for the instructors in your table, then use USERNAME() function to implement dynamic row level security as described in the following similar blog.

http://radacad.com/dynamic-row-level-security-with-power-bi-made-simple

Regards,
Lydia

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

View solution in original post

Tadder Helper II
Helper II

Re: Dynamic RLS

I handle Dynamic RLS using PATHCONTAINS() and USERPRINCIPALNAME() functions. I added a calculated column to my “users” table.

MgrPath = PATH([user_id],[user_mgr]). the data for the arguments are columns coming from SQL. This creates a custom column showing the “path” of the user all the way to the last manager. in other words it traces the current user and stores each manager. So if Mike reports to John reports to Kevin reports to Henry – the resulting data field would yield , read from left to right: “Henry|Kevin|John|Mike”

Once I did that, the RLS goes on the users table. simply:
ViewAs.jpg

What this does:
1. Look up the user’s email address (who are they logged in as?). Bring back the corresponding user_id (Mike)
2. If that user_id exists in the newly created MgrPath field, return that record. This means if I’m logged in as Kevin I will see all of Mike’s data AND all of John’s data (and anyone else who is managed by John). If I’m logged in as Mike, I will only see Mike’s data – because Mike is at the end of the “path”.

 

People in management positions will see data for all users for whom they are in their "path".

 

Works like a charm!

odeddror Helper IV
Helper IV

Re: Dynamic RLS

Hi there,

Can you tell us please what table name and column name do you use in your SQL table?

My roll is based Departments like |22|24|44| and user principle assigh to these rolls

 

Thanks

Oded Dror

 

Tadder Helper II
Helper II

Re: Dynamic RLS

@odeddror I'm not sure I understand your question fully..  The calculated column MgrPath = PATH([user_id],[user_mgr]) is set in the USERS_REVERE table.  It uses two columns that come from a sql query, user_id and user_mgr.  the PATH function creates a | delimited string showing that user's managerial hierarchy.

 

Then the VLOOKUP and PATHCONTAINS functions simply looks for the current logged in users name in that string.  If their name appears then any data related to that record will show for the user.

odeddror Helper IV
Helper IV

Re: Dynamic RLS

Tadder,

 

Thank you

 

Oded Dror

boovda
Frequent Visitor

Re: Dynamic RLS

@Tadder Fantastic method, very effective and just what I was looking for. Thanks for sharing it's very much appreciated.

Highlighted
Tadder Helper II
Helper II

Re: Dynamic RLS

Wish I could say it was my idea Smiley Happy but it's wasn't.  glad it worked out for you guys!!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

Top Solution Authors
Top Kudoed Authors