Role Level Security (RLS) based on Country and/or other criteria

[IzBell]

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	Email	Team	Department	Country
1007	Alice	Alice@xx.com	aa	A	UK
1000	John	John@xx.com	ab	A	France
1001	Abby	Abby@xx.com	ac	B	France
1002	Ben	Ben@xx.com	ae	C	UK
1003	Sarah	Sarah@xx.com	ab	C	UK
1004	Jess	Jess@xx.com	ac	D	USA
1005	Bill	Bill@xx.com	aa	A	USA
1001	Mark	Mark@xx.com	ab	B	France
1005	Bob	Bob@xx.com	ac	A	USA
1006	James	James@xx.com	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	Alice@xx.com	aa	A	UK
  1000	John	John@xx.com	ab	A	France
  1001	Abby	Abby@xx.com	ac	B	France
  1002	Ben	Ben@xx.com	ae	C	UK
  1003	Sarah	Sarah@xx.com	ab	C	UK
  1004	Jess	Jess@xx.com	ac	D	USA
  1005	Bill	Bill@xx.com	aa	A	USA
  1001	Mark	Mark@xx.com	ab	B	France
  1005	Bob	Bob@xx.com	ac	A	USA
  1006	James	James@xx.com	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	Abby@xx.com	ac	B	France
  1001	Mark	Mark@xx.com	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	Jess@xx.com	ac	D	USA

Your help would be much appreciated.
Many thanks,
IzBell

[Brunner_BI]

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.