Forum Discussion
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?
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
7 Replies
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,
LydiaI 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!