Row Level Security based on organisation structure - multiple conditions

Hi IzBell ,

Please try the formula below:

VAR CurrentUser = USERPRINCIPALNAME()

 // Read the current user’s values from the RLS table
 VAR ViewAllFlag =
  CALCULATE( MAX( 'RLS'[View All?] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )

 VAR RLS_EmployeeID =
  CALCULATE( MAX( 'RLS'[Employee ID] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )

 VAR AllowedOrgUnit =
  CALCULATE( MAX( 'RLS'[Org Unit] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )
 VAR ExcludedOrgUnit =
  CALCULATE( MAX( 'RLS'[Org Unit - Exclusion] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )

 VAR AllowedTeam =
  CALCULATE( MAX( 'RLS'[Team] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )
 VAR ExcludedTeam =
  CALCULATE( MAX( 'RLS'[Team - Exclusion] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )

 VAR AllowedDepartment =
  CALCULATE( MAX( 'RLS'[Department] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )
 VAR ExcludedDepartment =
  CALCULATE( MAX( 'RLS'[Department - Exclusion] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )

 // (Similarly do for Function and Role if needed)
 VAR ExcludedFunction =
  CALCULATE( MAX( 'RLS'[Function - Exclusion] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )
 VAR ExcludedRole =
  CALCULATE( MAX( 'RLS'[Role - Exclusion] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )

 // Build the allowed conditions for dimension matches.
 // If an allowed value is provided then only matching rows will be visible.
 // Also, if an exclusion value exists, rows with that value get filtered out.
 VAR OrgUnitCondition =
  ( ISBLANK( AllowedOrgUnit ) || 'Data'[Org Unit] = AllowedOrgUnit )
   && ( ISBLANK( ExcludedOrgUnit ) || 'Data'[Org Unit] <> ExcludedOrgUnit )

 VAR TeamCondition =
  ( ISBLANK( AllowedTeam ) || 'Data'[Team] = AllowedTeam )
   && ( ISBLANK( ExcludedTeam ) || 'Data'[Team] <> ExcludedTeam )

 VAR DepartmentCondition =
  ( ISBLANK( AllowedDepartment ) || 'Data'[Department] = AllowedDepartment )
   && ( ISBLANK( ExcludedDepartment ) || 'Data'[Department] <> ExcludedDepartment )

 VAR FunctionAndRoleExclusion =
  ( ISBLANK( ExcludedFunction ) || 'Data'[Function] <> ExcludedFunction )
   && ( ISBLANK( ExcludedRole ) || 'Data'[Role] <> ExcludedRole )

 RETURN
  // A row is visible if either the user is a “view all” user,
  // OR if all the matching conditions are met,
  // AND the Data row does not belong to the current user.
  ( ViewAllFlag = "Y" ||
   ( OrgUnitCondition &&
    TeamCondition &&
    DepartmentCondition &&
    FunctionAndRoleExclusion )
  )
  && ('Data'[Employee ID] <> RLS_EmployeeID)

Best Regards