Row Level Security: Permitting Access to same row for multiple User Principal Names

[MJEnnis]

Part of my model deals with course evaluations submitted by students. I have created a report so that teachers can view all historical data related to their own course evaluations and compare their results with aggregated/historical data via filters. I want to use row level security to restrict access to comments so that teachers can only view comments left by students for their (the teachers') own courses only. (They should not be able to read the comments left for other teachers.) This was straightforward, except that multiple teachers can teach the same course and I cannot figure out how to account for this in the code.

Approximantions of the pertinent tables are shown below. Due to various measures and visualizations (involving other sections of the model), it is not possible to alter the relationships between them. Suffice it to say, there is no relationship connecting the UPN to the comments.

‘EMPLOYEES’ = table of all employees, one row/unique ID per employee

Employee ID	UPN
1	BobM@institution.com
2	SueF@institution.com
3	FrankD@institution.com
4	KatZ@institution.com
5	KurtN@institution.com

‘TEACHERS’ = table of all course modules taught by each teacher, Module IDs, like Employee IDs, are unique, but both are duplicated in this table as a course can have two teachers and teachers can teach multiple modules

Module ID	Employee ID
1	1
1	2
2	3
2	4
3	5
4	1
5	4

‘MODULES’ = a list of all course modules ever taught, each with a unique ID, but some courses have multiple modules

Module ID	Course ID
1	1
2	2
3	3
4	4
5	4

‘COURSES’ = a list of all courses ever taught, each with a unique ID

Course ID

1

2

3

4

‘COMMENTS’ = a table containing all comments left on the course evaluations with the course ID identifying which course

Course ID	Comment
1	Bad course!
1	Good course!
2	Bad course!
2	Good course!
3	Bad course!
3	Good course!
4	Bad course!
4	Good course!

Using the code below, I have successfully matched SSO login usernames of teachers to course IDs associatied with each comment. However, since there can be two teachers for the same course, the comments can only be viewed by the teacher with the “MAX” EMPLOYEE ID in the MODULES table. How can I also add MIN(‘TEACHERS'[EMPLOYEE ID]) to this code so that both teachers can see their comments in the report? What if, in rare cases, I have three teachers for the same course? Shouldn't happen now, but is theoretically possible.

LOOKUPVALUE(EMPLOYEES[UPN], EMPLOYEES[EMPLOYEE Id], CALCULATE(MAX(‘TEACHERS'[EMPLOYEE ID]), FILTER(‘ TEACHERS', CALCULATE(MAX('MODULES'[COURSE ID]), FILTER('MODULES', 'MODULES'[Module ID] = ‘TEACHERS'[MODULE ID])) = COMMENTS[COURSE ID]))) = userprincipalname()

The only other solution I can think of is to CROSSJOIN the UPNs into the comments table.

Any better ideas? This is my first time applying row level security to a model.

[jaweher899]

One approach to solve this problem is to use a combination of the MIN and MAX functions in your row level security formula. You can modify the existing formula to include both the MIN and MAX of the 'TEACHERS'[EMPLOYEE ID] column, like this:

MIN(‘TEACHERS'[EMPLOYEE ID]) = CALCULATE(MIN(‘TEACHERS'[EMPLOYEE ID]), FILTER(‘ TEACHERS', CALCULATE(MAX('MODULES'[COURSE ID]), FILTER('MODULES', 'MODULES'[Module ID] = ‘TEACHERS'[MODULE ID])) = COMMENTS[COURSE ID])) OR MAX(‘TEACHERS'[EMPLOYEE ID]) = CALCULATE(MAX(‘TEACHERS'[EMPLOYEE ID]), FILTER(‘ TEACHERS', CALCULATE(MAX('MODULES'[COURSE ID]), FILTER('MODULES', 'MODULES'[Module ID] = ‘TEACHERS'[MODULE ID])) = COMMENTS[COURSE ID]))

This formula first filters the 'TEACHERS' table to include only the rows that have the same course ID as the comment being evaluated, then it calculates the MIN and MAX of the 'TEACHERS'[EMPLOYEE ID] column. The final step is to check if the current user's UPN is equal to the UPN of the employee with the MIN or MAX employee ID. This will allow both teachers of a course to see the comments for that course.

Another way you can solve this is by creating a separate table that holds the relationship between the course and the teachers, then use the USERPRINCIPALNAME() function along with the USERNAME() function to check the current user's UPN and username against the teachers UPN and username in that table and check if the course id matches.

For example:

COURSES_TEACHERS = CROSSJOIN(COURSES,TEACHERS)
USERPRINCIPALNAME() = CALCULATE(MAX(COURSES_TEACHERS[UPN]),FILTER(COURSES_TEACHERS,COURSES_TEACHERS[COURSE ID] = COMMENTS[COURSE ID]))

[MJEnnis · Accepted solution]

@jaweher899 Although your code specifically did not work, it helped me better understand how DAX is parsed in the row level security. For instance, I did not undertand that you could use OR() / || ! Thanks a lot. 

This is the code that seems to work:

LOOKUPVALUE(EMPLOYEES[UPN], EMPLOYEES[EMPLOYEE Id], CALCULATE(MAX(‘TEACHERS'[EMPLOYEE ID]), FILTER(‘ TEACHERS', CALCULATE(MAX('MODULES'[COURSE ID]), FILTER('MODULES', 'MODULES'[Module ID] = ‘TEACHERS'[MODULE ID])) = COMMENTS[COURSE ID]))) = userprincipalname()

||

LOOKUPVALUE(EMPLOYEES[UPN], EMPLOYEES[EMPLOYEE Id], CALCULATE(MIN(‘TEACHERS'[EMPLOYEE ID]), FILTER(‘ TEACHERS', CALCULATE(MAX('MODULES'[COURSE ID]), FILTER('MODULES', 'MODULES'[Module ID] = ‘TEACHERS'[MODULE ID])) = COMMENTS[COURSE ID]))) = userprincipalname()

thanks a lot! 

[MJEnnis]

Thanks for this suggestion. 

But I do not understand how this code relates to the UPNs.

MIN(‘TEACHERS'[EMPLOYEE ID]) = CALCULATE(MIN(‘TEACHERS'[EMPLOYEE ID]), FILTER(‘ TEACHERS', CALCULATE(MAX('MODULES'[COURSE ID]), FILTER('MODULES', 'MODULES'[Module ID] = ‘TEACHERS'[MODULE ID])) = COMMENTS[COURSE ID])) OR MAX(‘TEACHERS'[EMPLOYEE ID]) = CALCULATE(MAX(‘TEACHERS'[EMPLOYEE ID]), FILTER(‘ TEACHERS', CALCULATE(MAX('MODULES'[COURSE ID]), FILTER('MODULES', 'MODULES'[Module ID] = ‘TEACHERS'[MODULE ID])) = COMMENTS[COURSE ID]))