Dynamic RLS by group

[PhillipRothwell]

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

[Anonymous · Accepted solution]

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

[Sumanth_23]

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 

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls#:~:text=Row%2Dlevel%20security%20(....

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[Sumanth_23]

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!