Forum Discussion

supergallager34's avatar
supergallager34
Frequent Visitor
6 years ago

Row Level Dax

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:

 


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.

 
 
 
 
 

2 Replies

    • supergallager34's avatar
      supergallager34
      Frequent Visitor
      6 years ago

      Thank you for the links, I kind of have gone through them a few million times by now and have not been able to come up with a proper solution to my question. Also, I am kind of in a deadline that I need to meet. I have a kind of tricky situation. I feel like the solution should be pretty easy and I am not looking at it in the correct order.  I am going to start explaining from the beginning. 
      I have a table that contains the general student information. 



      StudentIDEnter DateLeave DateYearSchool IDGradeGender
      102Wednesday, August 14, 2019Sunday, May 17, 2020202040212Male
      103Tuesday, August 20, 2019Sunday, May 17, 2020202040312Female
      104Wednesday, August 14, 2019Thursday, May 21, 2020202040310Female
      105Monday, August 19, 2019Sunday, May 17, 202020204038Female
      106Wednesday, August 14, 2019Thursday, May 21, 202020204035Female
      108Saturday, August 17, 2019Wednesday, May 20, 202020204027Female
      109Tuesday, August 20, 2019Sunday, May 17, 202020204023Male
      110Tuesday, August 20, 2019Sunday, May 17, 202020204023Female
      115Thursday, August 15, 2019Monday, May 18, 202020204027Male
      114Thursday, August 15, 2019Monday, May 18, 202020204035Male
      119Friday, August 16, 2019Sunday, May 17, 202020204024Female
      120Wednesday, August 14, 2019Thursday, May 21, 2020202040310Male
      125Wednesday, August 14, 2019Thursday, May 21, 202020204034Male
      154Tuesday, August 20, 2019Tuesday, May 19, 202020204026Female
      150Monday, August 19, 2019Wednesday, May 20, 202020204029Female
      160Thursday, August 15, 2019Wednesday, May 20, 2020202040211Male
      140Thursday, August 15, 2019Monday, May 18, 202020204021Male
      139Thursday, August 15, 2019Monday, May 18, 202020204021Male
      137Wednesday, August 14, 2019Monday, May 18, 202020204031Female
      145Friday, August 16, 2019Monday, May 18, 202020204022Male
      144Wednesday, August 14, 2019Sunday, May 17, 202020204038Male
      165Tuesday, August 20, 2019Thursday, May 21, 202020204033Female
      177Tuesday, August 20, 2019Thursday, May 21, 202020204037Female
      189Thursday, August 15, 2019Wednesday, May 20, 2020202040211Female
      190Monday, August 19, 2019Wednesday, May 20, 2020202040212Male


      I am trying to create row level security for teachers and principals(Teachers being level 1 and Principals Level 2). The teachers should have access to see information to only the student they have in their class whereas the principal should have access to all the students within the school. 

      My teachers table looks somewhat like this, 

      EmployeeIDSchoolIDTeacherIDPerson TitleLevelStudentID
      4568402998English Teacher1102
      4789403965Math Teacher1103
      4789403965Math Teacher1104
      4598403999English Teacher1105
      4789403965Math Teacher1106
      4568402998English Teacher1108
      4566402921Math Teacher1109
      4568402998English Teacher1110
      4566402921Math Teacher1115
      4598403999English Teacher1114
      4566402921Math Teacher1119
      4598403999English Teacher1120
      4789403965Math Teacher1125
      4568402998English Teacher1154
      4566402921Math Teacher1150
      4568402998English Teacher1160
      4566402921Math Teacher1140
      4566402921Math Teacher1139
      4598403999English Teacher1137
      4566402921Math Teacher1145
      4789403965Math Teacher1144
      4598403999English Teacher1165
      4789403965Math Teacher1177
      4568402998English Teacher1189
      4568402998English Teacher1190


      And, the table above is the only table that has the teacher and students with their IDs on the same table. 
      I have a directory table as below, 

      EmployeeIDSchoolIDPerson TitleUSERPRINCIPALNAMELevel
      5002402Principal[email protected]2
      5003402Assistant Principal[email protected]2
      5070403Principal[email protected]2
      5879403Assistant Principal[email protected]2
      4566402Math Teacher[email protected]1
      4568402English Teacher[email protected]1
      4598403English Teacher[email protected]1
      4789403Math Teacher[email protected]1


      I have tried all kinds of different scenarios but there I have not been able to come up with a proper solution to create RLS for them so I am providing a sample of the tables as they are here to see if there is a way around. I have tried creating bridge tables and calling them by USERPRINCIPALNAME and Level but have not been able to find a way to limit the teachers from accessing all the student's records.. I have a deadline coming up and any help would be very much appreciated.