Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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)
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)
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |