Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
I have attached my sample file for you to look at.
@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.
I have attached my sample file for you to look at.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |