Dynamic RLS with conditions

[taikvei]

Hello,

I have a dynamic RLS set up now with a users table related to a data table on 'department' and RLS with USERPRINCIPALNAME().

this works fine.

I have now added a column in my users table that should designate what level of filtering is required - this is either 'department' and 'team', so I have two tables:

Users

user	department	team	level of access
1	dep1	team1	all
2	dep1	team2	all
3	dep1	team1	team
4	dep1	team1	team
4	dep1	team2	team

Data

department	team	number
dep1	team1	11
dep1	team2	22
dep1	team3	33

Is it possible for me to set up my RLS to where

IF(level of access = team, then filter data table so that 'data'[team]='users'[team], if not filter to department)

So users 1 and 2 should see all of the data, user 3 should see only team1 data, and user 4 should only see team1 and team 2?
Manually setting this up is not feasible, as my actual list of users can change, and is at > 100 users now.

[taikvei · Accepted solution]

FIgured it out now.

In case anyone else is looking for a solution in the future:

I set up a column in my data table, which would return True/False (but set the column data type as text) :
RLSTeam = CALCULATE( COUNTROWS('Users'), FILTER( 'Users', 'Users'[Team] = [Team] && 'Users'[Access Level]="Team") ) > 0

And in my RLS, on the data table, the following:

var Filtered = FILTER('Users','Users'[Email] = USERPRINCIPALNAME())
var TeamLevel = CONTAINS(Filtered,'Users'[Access Level],"Team")
return
IF(IF(TeamLevel,1,0)=1, [RLSTeam] = "TRUE",true)