Multi condition RLS formula

[Anonymous]

Hi everyone,

I want to implement an a dynamic row level security and I need your help.

I have two tables

I want to put a condition, if the user is a manager or belong to the departement_ID=3 to see all the data, else to see only his departement data.

My dax formula does not work. I don't know how to define the "NO FILTER".

if ([Role]<>"manager" || [Departement_ID]<>1,
[User_name] = USERNAME(),
"NoFilter" )

that's my relationship, the bidirectionnal security is enabled

Thanks

[jdbuchanan71 · Accepted solution]

@Anonymous 

You don't actually want to filter the user table, you want to filter the revenues table and you don't need the users table joined to the revenues table.  You can write your security meaure like this.

VAR _UN = USERNAME()
VAR _Role = LOOKUPVALUE(Users[Role],Users[User_name],_UN)
VAR _Dept = LOOKUPVALUE(Users[Department_ID],Users[User_name],_UN)

RETURN
SWITCH(
    TRUE(),
    _Role = "manager",TRUE(),
    _Dept = 3,TRUE(),
    Revenues[Dept_ID] = _Dept,TRUE()
)

And this gets applied to your revenues table.

Basically, if the role = manager or the users dept id = 3 it will return TRUE for every row in the revenues table.  RLS shows only rows where the filter is TRUE so it will show every row.  If it is an employee in another dept it finds their dept ID and compares it to the dept id of the revenues table which will only return TRUE if it is their dept.

Manager in dept 1

worker in dept 3

worker in dept 4

I have attached my sample file for you to look at.

[Anonymous]

@jdbuchanan71 thanks a lot

it works fine 🙂