Forum Discussion
Role Level Security (RLS) based on Country and/or other criteria
Good day everyone,
I would like to create a DAX measure under Manage Security Roles with the following logic:
- A small group of users can see all data in the table.
- The rest can only see data for the country they are based in, and with some additional exclusion criteria.
For example, I have a table called "Employee_List":
- Anyone in Team = "aa" AND Department = "A", they can see everything.
- The rest can see data for the country they are based in, except for data in Department = "A"
| Employee ID | Employee Name | Team | Department | Country | |
| 1007 | Alice | [email protected] | aa | A | UK |
| 1000 | John | [email protected] | ab | A | France |
| 1001 | Abby | [email protected] | ac | B | France |
| 1002 | Ben | [email protected] | ae | C | UK |
| 1003 | Sarah | [email protected] | ab | C | UK |
| 1004 | Jess | [email protected] | ac | D | USA |
| 1005 | Bill | [email protected] | aa | A | USA |
| 1001 | Mark | [email protected] | ab | B | France |
| 1005 | Bob | [email protected] | ac | A | USA |
| 1006 | James | [email protected] | ad | A | France |
For example:
- Alice is in Team = aa and Department = A, and she can see everything. When she logs in, she can see data for all employees. It will be the same for Bill.
Employee ID Employee Name Email Team Department Country 1007 Alice [email protected] aa A UK 1000 John [email protected] ab A France 1001 Abby [email protected] ac B France 1002 Ben [email protected] ae C UK 1003 Sarah [email protected] ab C UK 1004 Jess [email protected] ac D USA 1005 Bill [email protected] aa A USA 1001 Mark [email protected] ab B France 1005 Bob [email protected] ac A USA 1006 James [email protected] ad A France - James is based in France. When he logs in can only see employees in France - excluding Department A.
Employee ID Employee Name Email Team Department Country 1001 Abby [email protected] ac B France 1001 Mark [email protected] ab B France
- Bob is based in USA. When he logs in can only see employees in USA- excluding Department A.
Employee ID Employee Name Email Team Department Country 1004 Jess [email protected] ac D USA
Your help would be much appreciated.
Many thanks,
IzBell
1 Reply
This is quite complex that is why you do not get an answer quickly here.
Here are the conditions:
- Anyone in Team = "aa" AND Department = "A", they can see everything.
- The rest can see data for the country they are based in, except for data in Department = "A"
The first one is easy but this is not something you can solve with RLS. You need to create a security group in the active directory for these people. Alternatively, add them as contributors to the workspace so RLS does not even apply OR add them manually (i dont recommend this) to the "none" group or create a group in RLS without any filter. RLS is more about restricting data than giving full access.
For your second condition I would also set up two AD groups, one for all other users and one for department A specifically.
Then you set up RLS based on the country and just assign the "other than department A" people to that role.
