Forum Discussion
Dynamic RLS by group
Guidance would be much appreciated as I've not previously implemented RLS
I've created a data visualisation presenting aggregated data at a school level and am automating how users can view this using Row Level Security Based on the "USERNAME()" returned value. AT present the tables are seperated out between staff and student and I would like to filter data visibility based on staff username and associated School membership.
So for example the two tables
| Username() | Staff School |
| User1 | aaa |
| User2 | bbb |
| User3 | bbb |
| User4 | ccc |
| User5 | aaa |
| Student School | Student Data |
| aaa | svdffdsfs |
| aaa | sdfsdfdsf |
| bbb | scfewfew |
| bbb | wffefsfd |
| ccc | sdffewefe |
| bbb | sefwefesf |
| ccc | sefwfwefwef |
So if Usern is the returned value. How best to apply RLS so as each staff member can view the school records that only apply to them?
Thanks in advance
PhillipRothwell
To use USERNAME() you need Username column in the format of power bi email account.Some guidance might help:
1. Create a relationship between the schools column for the 2 tables
2. Create the RLS rule in PBI Desktop make Username = USERNAME().
3. Publish to PBI Service, add each users in the workspace with Viewer role
4. Assign the users to the RLS role in Dateset => Security.
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
hi PhillipRothwell - yuo can follow the Microsoft documentation to implement RLS. You can create roles and assign folks to specific roles based on how users need to view the data
Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!
hi PhillipRothwell - Do let me know if the previous post was clear or you need any additional information regarding setup of RLS.
Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!
PhillipRothwell
To use USERNAME() you need Username column in the format of power bi email account.Some guidance might help:
1. Create a relationship between the schools column for the 2 tables
2. Create the RLS rule in PBI Desktop make Username = USERNAME().
3. Publish to PBI Service, add each users in the workspace with Viewer role
4. Assign the users to the RLS role in Dateset => Security.
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.