Dynamic RLS

[renanpinheiro]

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?

[v-yuezhe-msft · Accepted solution]

@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

[Tadder]

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:

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!

[boovda]

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

[Tadder]

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

[Anonymous]

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]

@Anonymous 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.

[Anonymous]

Tadder,

Thank you

Oded Dror