Dynamic Row Level Security

[obriaincian]

Hi All,

I'm a bit puzzled and cannot get my head around how to set up dynamic RLS for the below example.

I want to use USERPRINCIPALNAME() and determine if the user is a manager or director (use the roles table for this).

If they are a director I want to filter the Director Email in the data table for the email returned by USERPRINCIPALNAME(). If they are a manager I want to filter the Manager Email for the email returned by USERPRINCIPALNAME(). 

Any help will be greatly appreciated

I have 2 tables (my dataset is confidential so I'll create dummy data below)

Table 1: Roles Table

Name	Email	Role
Michael Dunne	Michael@123.ie	Director
Ann Brogan	Ann@123.ie	Manager
Julie Murray	Julie@123.ie	Manager
Ben Marsh	Ben@123.ie	Director

Table 2: Data Table

Employee	Email	Manager Email	Director Email
Michael Dunne	Michael@123.ie	Michael@123.ie	Michael@123.ie
Ann Brogan	Ann@123.ie	Ann@123.ie	Michael@123.ie
Julie Murray	Julie@123.ie	Julie@123.ie	Michael@123.ie
Stephen Fry	Stephen5@123.ie	Ann@123.ie	Ben@123.ie

[nandukrishnavs]

@obriaincian  Try below DAX expression 

VAR _role=MAXX(FILTER('Roles Table',[Email]=USERPRINCIPALNAME()),[Role])
VAR _filter = SWITCH(_role,
"Director", [Director Email] = USERPRINCIPALNAME(),
"Manager",[Manager Email] =USERPRINCIPALNAME(),
FALSE())
RETURN _filter

[obriaincian]

@nandukrishnavs thank you, this is great, can I ask you one last question.

I also have instances where one user can be both a Director and Manager, in this instance I'd want to filter the data for where [Director Email] = USERPRINCIPALNAME() &&

                        [Manager Email] = USERPRINCIPALNAME() 

I modified the code to look like this but it's not working

VAR _role=MAXX(FILTER('Roles Table',[Email]=USERPRINCIPALNAME()),[Role])
VAR _filter = SWITCH(_role,
"Director and Manager", [Workstream DRI Email] = USERPRINCIPALNAME() && [Hiring Manager Email] =USERPRINCIPALNAME(),
"DRI", [Workstream DRI Email] = USERPRINCIPALNAME(),
"Hiring Manager",[Hiring Manager Email] =USERPRINCIPALNAME(),
FALSE())
RETURN _filter

Thanks

[nandukrishnavs · Accepted solution]

@obriaincian 

VAR _roles=SELECTCOLUMNS(FILTER('Roles Table',[Email]=USERPRINCIPALNAME()),"@Role",[Role])
VAR _filter = SWITCH(TRUE(),
"Director" IN _roles && "Manager" IN _roles, [Director Email] = USERPRINCIPALNAME() && [Manager Email] =USERPRINCIPALNAME(),
"Director" IN _roles, [Director Email] = USERPRINCIPALNAME(),
"Manager" IN _roles, [Manager Email] =USERPRINCIPALNAME(),
FALSE())
RETURN _filter

If required use || insted of && in the red marked place. If you use AND (&&), data will be available only both conditions are true. I think you need OR (||) condition.