SSAS/Power BI Row Level Security

I have been using some very useful articles created by radacad (e.g. https://radacad.com/dynamic-row-level-security-with-manager-level-access-in-power-bi) to enable me to add some row level security to my SSAS data model that is the data source for a Power BI dashboard.

In essence, this is how I want my row level security to work.

1. If the logged in user is a manager and their department is LOD1, they can see the data for the whole organisation.
2. If the logged in user is a manager, they see their own data and the data of those in the same department as them.
3. If the logged in user is not a manager, they only see their own data.

The above represents each If statement in the below.

The following DAX near enough works perfect for this:

=
If(
MaxX(
Filter(
'Employee',
'Employee'[Email]=USERPRINCIPALNAME() )
,'Employee'[IsManager])="Yes" 
&&
MaxX(
Filter(
'Employee',
'Employee'[Email]=USERPRINCIPALNAME() )
,'Employee'[Department])="LOD1" 
,1=1
,
If(
MaxX(
Filter(
Employee,
[Email]=USERPRINCIPALNAME())
, Employee[IsManager])="Yes" 
&& 
MaxX(
Filter(
Employee,
[Email]=USERPRINCIPALNAME())
, Employee[Department]) <> "LOD1",
PATHCONTAINS(Employee[Department],
MaxX(
Filter(
Employee,
[Email]=USERPRINCIPALNAME())
, Employee[Department]
)),
[Email]=USERPRINCIPALNAME()))

However, the issue I have comes with using the MaxX expression. This is because within the Employee table, an employee can have more than one row, because an employee can have more than one department. Therefore, for those employees with more than one department, the Power BI dashboard is only showing them the data for the max dept selected.

If anyone could help me tweak my query to accommodate this obstacle and allow staff with more than one dept to see all their depts, not just the max, this would be great.

Thanks

You can use DISTINCT or VALUES to get the list of departments for the user, then do a IN in the filter conditional.

Try:

var isManagerLOD1 = 
  COUNTROWS(
    FILTER(Employee,
      'Employee'[Email] = USERPRINCIPALNAME() &&
      'Employee'[IsManager] = "Yes" &&
      'Employee'[Department] = "LOD1" 
    )
  ) > 0

var managerDepartments =
  CALCULATETABLE(
    DISTINCT('Employee'[Department]),
    FILTER(Employee,
      'Employee'[Email] = USERPRINCIPALNAME() &&
      'Employee'[IsManager] = "Yes"
    )
  )

var isManagerDepartment = [Department] IN managerDepartments

var isUser = [Email] = USERPRINCIPALNAME()

return isManagerLOD1 || isManagerDepartment  || isUser