Configure Row Level Security based on Country of the logged-in User

[IzBell]

Hi everyone,

I am wondering if anyone can help with the DAX for the Manager Roles in Power BI Desktop please.

Logic: The RLS where the logged-in user can see data for the Country OR Department they are based in, except their own records.

For example: I have two tables [Employee Table] and [Sale Table]

• [Employee Table]

Employee ID	Email	Country	Department
1	aa@x.com	Australia	HR
2	ab@x.com	Canada	H&S
3	ac@x.com	Thailand	Finance
4	ad@x.com	South Africa	Payroll

• [Sale Table]

Date	Employee ID	Email	Country	Department	Sales Amount
Sep-24	1	aa@x.com	Australia	HR	$                    1,000
Sep-24	2	ab@x.com	Canada	H&S	$                         500
Sep-24	3	ac@x.com	Thailand	Finance	$                    1,500
Sep-24	4	ad@x.com	South Africa	Payroll	$                         300
Sep-24	5	ae@x.com	Australia	HR	$                    1,500
Sep-24	6	af@x.com	Canada	HR	$                         200
Sep-24	7	ag@x.com	Australia	HR	$                    4,000
Oct-24	1	aa@x.com	Australia	HR	$                         500
Oct-24	2	ab@x.com	Canada	H&S	$                         300
Oct-24	3	ac@x.com	Thailand	Finance	$                    4,000
Oct-24	4	ad@x.com	South Africa	Payroll	$                         500
Oct-24	5	ae@x.com	Australia	HR	$                    2,500
Oct-24	6	af@x.com	Canada	HR	$                    4,010
Oct-24	7	ag@x.com	Australia	H&S	$                    1,000

If the User 1 (aa@x.com) logs in, they can see data for Australia OR HR, except their own records.
Expected Result:

Date	Employee ID	Email	Country	Department	Sales Amount
Sep-24	5	ae@x.com	Australia	HR	$                    1,500
Sep-24	6	af@x.com	Canada	HR	$                         200
Sep-24	7	ag@x.com	Australia	HR	$                    4,000
Oct-24	5	ae@x.com	Australia	HR	$                    2,500
Oct-24	6	af@x.com	Canada	HR	$                    4,010
Oct-24	7	ag@x.com	Australia	H&S	$                    1,000

Your help would be much appreciated.

Many thanks,

IzBell

[Anonymous · Accepted solution]

Hi @IzBell ,

I think you can try code as below.

(
[Country] ==  CALCULATE(MAX(Employee[Country]),FILTER(ALLSELECTED(Employee),Employee[Email] = USERPRINCIPALNAME()))
|| 
[Department] ==
CALCULATE(MAX(Employee[Department]),FILTER(ALLSELECTED(Employee),Employee[Email] = USERPRINCIPALNAME()))
)
&&
[Email] <> USERPRINCIPALNAME()

Result is as below. (View as aa@x.com)

Best Regards,
Rico Zhou

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

[IzBell]

Amazing. This is working as expected. Thank you very much Rico. Much apprecaited.
Kind regards,

IzBell