Tricky Row Level Security Problem

[MellishT]

Hi! I am developing in Power BI and I came upon a tricky RLS situation that I can't quite figure out. Thank you for taking the time to read this!

My data model consists of many different datasets, with Demographics  being the center of the star, with that being a one-student per row table that connects to every table in the model via a unique ID. This dataset is only the current data for students, and ti updated frequently, replacing old data. The other dataset that matters for this is the Assessments dataset, which contains all assessment data formatted as one row per assessment. This contains data from many years.

The security needs for this model are the following:
- Teachers must be able to see all data for all students currently in their school (uses the "school" column in demographics)
- Teachers must be able to see all assessments tested at their school, even for students who are no longer at that school (uses the "school" column in assessments)

I am struggling to find a way to achieve both of these objectives, as filtering the demographics dataset removes some assessments for graduated students or students who changed schools, but it is necessary to filter demographics in order to protect the many many other datasets aside from assessments. I tried using multiple rows, one for assessments and one for demographics, but this allowed teachers to see all data since the assessments role did not have any filters on demographics. I believe that if you have filters on mutliple datasets in the same security role it is not additive, but a intersection of the two conditions? If I am wrong about that please let me know.

Thanks for reading, and if you can help in any way it would be greatly appreciated!

[Ilgar_Zarbali · Accepted solution]

If you need to manage row-level security (RLS) so that teachers can see:

• All current students in their school, and
• All assessments conducted at their school (even for students who have left),
  you can handle this cleanly by using two roles and combining their permissions.

Create two roles and assign teachers to both.

Role A – CurrentStudents
Filter the Demographics table by the teacher’s school:

Demographics[School] IN
CALCULATETABLE(VALUES(Users[School]), Users[UPN] = USERPRINCIPALNAME())

This ensures they only see data for students currently in their school.

Role B – AssessmentsAtMySchool
Filter the Assessments table by the school column:

Assessments[School] IN
CALCULATETABLE(VALUES(Users[School]), Users[UPN] = USERPRINCIPALNAME())

For all other tables in this role, set the filter to:

FALSE()

This prevents exposing other data while still allowing access to all assessments from that school.

How it works

• Power BI unions the results of both roles.
• Teachers see all data for current students plus all assessments from their school — even for students no longer there.

[MellishT]

Thank you! I didn't think about using FALSE() like that.

[amitchandak]

@MellishT , Refer if this can help
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

[MellishT]

Thank you for the response but I do not believe any path functions will help in my case, since I would still have the issue of assessment and demographic filters clashing.