Apply Dynamic RLS Multiple levels.

[quynhphan]

Hi there,
I have three tables with example data as below.
On my report, I just want to allow user to see only data of invoices which products belong to their department and it will be based on the employee data generated automatically. It means that whenever having new employee I don't need to assign them manually.
What should I do on DAX to create roles for this purpose?
Thanks

Product	Department
A	Sales
B	MKT
C	MKT
D	Sales
E	Supply Chain
F	Sales

Invoice_ID	Value	Product
1	1000	A
2	1500	C
3	2000	F
4	3500	D
5	2000	A
6	500	B

Emp_id	Mail	Department
X	[email protected]	Sales
Y	[email protected]	MKT
Z	[email protected]	Supply Chain

[lbendlin · Accepted solution]

In your RLS create roles per department. 

Each role should filter the Product Department table.

Role membership should be the distribution list of all department users.

Note:  This is static RLS, not dymanic RLS.

[Anonymous · Accepted solution]

Thanks to @lbendlin  for the detailed steps.

Hi @quynhphan ,
I have added an example for this, which I hope will help you understand and solve the problem better:

Relationship:

1.Manage roles, create three roles for three departments.

[Department] = "Sales"

[Department] = "MKT"

[Department] = "Supply Chain"

2.View as roles.

output:

In Power Bi Service, add groups who belong to the role.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

In your RLS create roles per department. 

Each role should filter the Product Department table.

Role membership should be the distribution list of all department users.

Note:  This is static RLS, not dymanic RLS.