topic Apply Dynamic RLS Multiple levels. in DAX Commands and Tips

Apply Dynamic RLS Multiple levels.

quynhphan — Fri, 12 Apr 2024 10:02:44 GMT

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	x@abc.com	Sales
Y	y@abc.com	MKT
Z	z@abc.com	Supply Chain

Re: Apply Dynamic RLS Multiple levels.

lbendlin — Sat, 13 Apr 2024 20:27:48 GMT

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.

Re: Apply Dynamic RLS Multiple levels.

Anonymous — Mon, 15 Apr 2024 06:30:52 GMT

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.