Tricky Row Level Security Problem

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.