Forum Discussion
Advocate IIDynamic 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
Helper III handle Dynamic RLS using PATHCONTAINS() and USERPRINCIPALNAME() functions. I added a calculated column to my “users” table.
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.
Tadder,
Thank you
Oded Dror
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!