Row Level Security in Schools

Hello,

I am trying to test out basic rls and I think I am almost there but I am having trouble trying to configure a DAX calculation for it. I have a table with Student ID, their basic information. And I have another table for teachers and principals. The teachers have a row for each period that a student has a class with them.So it is somewhat like this:

image
There is also a column with UserPrincipalName but I have not included that above.

** I have both the tables on a many to many relationship based on the school ID column from both the tables.

For the principals I have my dax calculated based on just the userprincipalname since they would get access to all the students. that are in the school ID that they have in their row. However, I am having trouble in trying to create a dax calculation for teachers.

check out this great video (about an hour long) on RLS logic, it specifically addresses the ‘many to many’ issue you are talking about (at roughly the 30 minute mark). There are problems with having many-to-many relationships in your model, which I’m sure you have seen discussed in other places in the forum.

Instead of keeping a complete many-to-many model, a ‘bridge’ table is used to eliminate that. If you can change to something like this, it should simplify writing your DAX to filter the teachers.

2 Likes

I have watched this video a couple of times. My only issue here is that I have a column for employee ID and then a different column for teacher ID if they are teachers and also a column from student ID if there are students assigned to the teachers. Thus if the employee ID row is for a principal, then the teacher ID and Student ID column is blank. I guess I am having a bit of a trouble designing the Bridge tables. I am really new to this so it seems a little difficult.

if you can build out a dummy model that demonstrates your issue, the team might be able to help you with this

I have created a sample dummy here. Thank you for trying to help.rowlevel_test.pbix (72.4 KB)

@supergallagher25

okay - looking at your model, I think the biggest piece you are missing here is a relationship between the student grade and the teacher, you have subject - but I’m sure that in your real model that would be much more ambiguous than just using the teacher title.

For now, I have to give this a rest and get back to some tasks I have to complete before the weekend, but I will check back in over my lunch break.

I am posting the sample as the original file looks or at least almost. There is no column for grades to be linked with the teachers.

so what information is the teacher permitted to see? All grades for the student regardless of what class the grade was earned in?

No, teacher would only see grades for students who are currently taking their classes which is where the teachers table comes in. I know it is very tricky and somewhat seems impossible to me.

It seems at this point, the first problem is not role level security - but just how to filter the student grades to the specific teacher level. Is there any cross-reference of a class ID and the student grade?

I think if you can get the student grades to filter - not worrying about the security level yet, then the security part will be easier.

okay, I do have a teacher grades table that I did not think that we would need that has student grades, grade received and teacher names and section ID of the class. Let me make a dummy of it and upload. Thank you so much for your time and consideration.

Here is the updated file. Once again, thank you so much. I am kind of on a time crunch and this means a lot.rowlevel_test.pbix (72.4 KB)

@supergallagher25

I am sorry I did not get back to you on this yesterday, but just after posting my last response, we had a massive storm and lost power and phone/internet for the day. I’ve got to spend today doing some necessary cleanup from the storm damage, and catching up on some work deadlines that I missed being without power.

looking at your revised model, I still don’t see a way to connect the teacher to the grades the students are receiving, did I miss something?

Hello,

Thank you so much for your time and consideration. I had somehow mistakenly uploaded the incorrect file. I am about to upload the correct file right away. I am sorry to hear about the storm, I hope everything it okay.rowlevel_test (2).pbix (77.8 KB)

Luckily, my home was spared, but some neighbors and co-workers were not so lucky, so I have been using my breaks from work to help breaking down fallen limbs and general cleanup. I never thought I would be dealing with hurricane force winds living in the US Midwest.

Anyway… please see the attached, not knowing exactly how you intend to handle the RLS (I’m assuming you will use something like USERPRINCIPLENAME ), I simply built a single role using one of the Teacher names.

As I expected, the devil in this was getting the tables properly connected.
For my solution, I created an index of SectionID_StudentID for the Grades, Teachers, and Students table (I duplicated your Students table, so look to tStudent(2) for my solution).

Also, instead of using the implicit ‘count’ of each of the tables that you had, I have created a Measure - I’m hoping you are already doing that in your full model and this was just for simplicity in the sample.

Table relationships were created, and I am not using bi-directional filters in any of them (I seriously recommend against bi-directional filters for performance reasons).
RowLevel - for Teachers.pbix (108.7 KB)

Hi @supergallagher25, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

Heather,

Thank you so much for the help. I really appreciate it. I apologize for the late response, I had family emergency last week and was not near the computer.
I saw that you did use the teacher name, and please forgive me in case I lack knowledge here, I did include a lookup column to pull in UPN in that table to do it by UPN.

However, when I had previously done with for principals, I had used School ID to connect to the profiles for them, I was wondering with the scenario of the datasets looking as they are at the moment, should I be creating different roles for principals based on their school ID since they only have access to their school? Thats what I did and just wanted to double check with you. Thank you so much! This means a lot.

The only reason I used Teacher Name instead of USERPRINCIPLENAME was because it was easier to test on my machine, you should be able to easily edit the role I created to use USERPRINCIPLENAME instead.

And yes, in your situation, I would probably create different roles for Principles than Teachers - but I’m not an expert in RLS by any means. Someone else may have been able to suggest a more elegant approach. :slight_smile:

1 Like

I apologize for the late response. I had a family emergency. Thank you so much. This helped a lot.

no worries - family always comes first, glad I could help

2 Likes