Row Level Security set-up

[heidibb]

Hello!

We are building a report for our faculty to manage their courses. But also we would like the faculty team leads (who are also teaching faculty) to be able to view the courses they are teaching, as well as all the courses their assigned faculty are teaching as well.

Ideally, we would want the following to happen with RLS:

When our faculty log in, it will be set up to show only their own sections and corresponding data. 

Faculty RLS role will be this

[FacultyEmail] = Username() || [FacultyAccount] = Username()

When Team Leads log in, we want them to see their own sections as well as all sections being taught by their assigned faculty

Team Lead RLS role should be something like this I think 

[TeamLeadEmail] = Username() ||
[TeamLeadAccount] = UserName() ||
[FacultyEmail] = Username() ||
[FacultyAccount] = Username()

The faculty role is pretty straight forward and working ok. The Team Lead role is a little trickier.

My data is set up similar to this.

Imagine that Faculty 1 is also Team Lead 4. When that person logs in, we want them to be able to see sections 1111, 2666, 2777, 2888, 2999 and 3000.

Would you say I have my Team Lead role logic set up to achieve this? 

[v-diye-msft · Accepted solution]

Hi @heidibb 

You might consider setting the hierarchy among Faculty and team leader, and Faculty outrank than the team leader, similar blog refer to:

https://radacad.com/dynamic-row-level-security-with-manager-level-access-in-power-bi

Also, it similar to apply OR condition into 2 slicers, I believe you can check out below blog for more hints:

https://www.sqlbi.com/articles/using-or-conditions-between-slicers-in-dax/