Forum Discussion
Row Level Dax
supergallager34 , refer if these can help
RLS - Row Level security
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns-in-Power/td-p/712613
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
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.
| StudentID | Enter Date | Leave Date | Year | School ID | Grade | Gender |
| 102 | Wednesday, August 14, 2019 | Sunday, May 17, 2020 | 2020 | 402 | 12 | Male |
| 103 | Tuesday, August 20, 2019 | Sunday, May 17, 2020 | 2020 | 403 | 12 | Female |
| 104 | Wednesday, August 14, 2019 | Thursday, May 21, 2020 | 2020 | 403 | 10 | Female |
| 105 | Monday, August 19, 2019 | Sunday, May 17, 2020 | 2020 | 403 | 8 | Female |
| 106 | Wednesday, August 14, 2019 | Thursday, May 21, 2020 | 2020 | 403 | 5 | Female |
| 108 | Saturday, August 17, 2019 | Wednesday, May 20, 2020 | 2020 | 402 | 7 | Female |
| 109 | Tuesday, August 20, 2019 | Sunday, May 17, 2020 | 2020 | 402 | 3 | Male |
| 110 | Tuesday, August 20, 2019 | Sunday, May 17, 2020 | 2020 | 402 | 3 | Female |
| 115 | Thursday, August 15, 2019 | Monday, May 18, 2020 | 2020 | 402 | 7 | Male |
| 114 | Thursday, August 15, 2019 | Monday, May 18, 2020 | 2020 | 403 | 5 | Male |
| 119 | Friday, August 16, 2019 | Sunday, May 17, 2020 | 2020 | 402 | 4 | Female |
| 120 | Wednesday, August 14, 2019 | Thursday, May 21, 2020 | 2020 | 403 | 10 | Male |
| 125 | Wednesday, August 14, 2019 | Thursday, May 21, 2020 | 2020 | 403 | 4 | Male |
| 154 | Tuesday, August 20, 2019 | Tuesday, May 19, 2020 | 2020 | 402 | 6 | Female |
| 150 | Monday, August 19, 2019 | Wednesday, May 20, 2020 | 2020 | 402 | 9 | Female |
| 160 | Thursday, August 15, 2019 | Wednesday, May 20, 2020 | 2020 | 402 | 11 | Male |
| 140 | Thursday, August 15, 2019 | Monday, May 18, 2020 | 2020 | 402 | 1 | Male |
| 139 | Thursday, August 15, 2019 | Monday, May 18, 2020 | 2020 | 402 | 1 | Male |
| 137 | Wednesday, August 14, 2019 | Monday, May 18, 2020 | 2020 | 403 | 1 | Female |
| 145 | Friday, August 16, 2019 | Monday, May 18, 2020 | 2020 | 402 | 2 | Male |
| 144 | Wednesday, August 14, 2019 | Sunday, May 17, 2020 | 2020 | 403 | 8 | Male |
| 165 | Tuesday, August 20, 2019 | Thursday, May 21, 2020 | 2020 | 403 | 3 | Female |
| 177 | Tuesday, August 20, 2019 | Thursday, May 21, 2020 | 2020 | 403 | 7 | Female |
| 189 | Thursday, August 15, 2019 | Wednesday, May 20, 2020 | 2020 | 402 | 11 | Female |
| 190 | Monday, August 19, 2019 | Wednesday, May 20, 2020 | 2020 | 402 | 12 | Male |
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,
| EmployeeID | SchoolID | TeacherID | Person Title | Level | StudentID |
| 4568 | 402 | 998 | English Teacher | 1 | 102 |
| 4789 | 403 | 965 | Math Teacher | 1 | 103 |
| 4789 | 403 | 965 | Math Teacher | 1 | 104 |
| 4598 | 403 | 999 | English Teacher | 1 | 105 |
| 4789 | 403 | 965 | Math Teacher | 1 | 106 |
| 4568 | 402 | 998 | English Teacher | 1 | 108 |
| 4566 | 402 | 921 | Math Teacher | 1 | 109 |
| 4568 | 402 | 998 | English Teacher | 1 | 110 |
| 4566 | 402 | 921 | Math Teacher | 1 | 115 |
| 4598 | 403 | 999 | English Teacher | 1 | 114 |
| 4566 | 402 | 921 | Math Teacher | 1 | 119 |
| 4598 | 403 | 999 | English Teacher | 1 | 120 |
| 4789 | 403 | 965 | Math Teacher | 1 | 125 |
| 4568 | 402 | 998 | English Teacher | 1 | 154 |
| 4566 | 402 | 921 | Math Teacher | 1 | 150 |
| 4568 | 402 | 998 | English Teacher | 1 | 160 |
| 4566 | 402 | 921 | Math Teacher | 1 | 140 |
| 4566 | 402 | 921 | Math Teacher | 1 | 139 |
| 4598 | 403 | 999 | English Teacher | 1 | 137 |
| 4566 | 402 | 921 | Math Teacher | 1 | 145 |
| 4789 | 403 | 965 | Math Teacher | 1 | 144 |
| 4598 | 403 | 999 | English Teacher | 1 | 165 |
| 4789 | 403 | 965 | Math Teacher | 1 | 177 |
| 4568 | 402 | 998 | English Teacher | 1 | 189 |
| 4568 | 402 | 998 | English Teacher | 1 | 190 |
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,
| EmployeeID | SchoolID | Person Title | USERPRINCIPALNAME | Level |
| 5002 | 402 | Principal | [email protected] | 2 |
| 5003 | 402 | Assistant Principal | [email protected] | 2 |
| 5070 | 403 | Principal | [email protected] | 2 |
| 5879 | 403 | Assistant Principal | [email protected] | 2 |
| 4566 | 402 | Math Teacher | [email protected] | 1 |
| 4568 | 402 | English Teacher | [email protected] | 1 |
| 4598 | 403 | English Teacher | [email protected] | 1 |
| 4789 | 403 | Math 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.