Row Level Security based on organisation structure - multiple conditions

[IzBell]

Hi Power BI Community,

I need some guidance on setting up Row-Level Security. I have two tables as below.
RLS table:

Employee ID	Email	View All?	Org Unit	Team	Department	Country	Org Unit - Exclusion	Team - Exclusion	Department - Exclusion	Function - Exclusion	Role - Exclusion
123	a@x.com		Brand	Marketing		Canada
123	a@x.com		Brand	Marketing		USA
456	b@x.com	Y
789	c@x.com		Brand	Marketing		Canada				HR	HR Manager
789	c@x.com		Brand	Marketing		USA				HR	HR Manager
345	d@x.com		Innovation	Tech	AI
578	e@x.com		Innovation	Tech	AI					HR	HR Manager
255	f@x.com		Innovation	Tech					AI
190	g@x.com		DC	Retail		Singapore				HR	HR Manager
285	h@x.com		DC					Retail		HR	HR Manager

Data table:

Employee ID	Amount	Org Unit	Team	Department	Country	Function	Role
101	100	Brand	Marketing	Comm	Canada	HR	HR Manager
102	100	Brand	Marketing	Comm	USA	Finance	Finance Manager
103	100	Brand	Marketing	Comm	Singapore	IT	IT Manager
104	100	Innovation	Tech	AI	Canada	HR	HR Manager
105	100	Innovation	Tech	AI	USA	Finance	Finance Manager
106	100	Innovation	Tech	AI	Singapore	IT	IT Manager
107	100	DC	Retail	Store	Canada	HR	HR Manager
108	100	DC	Retail	Store	USA	Finance	Finance Manager
109	100	DC	Retail	Store	Singapore	IT	IT Manager
110	100	DC	CI	Packaging	Canada	HR	HR Manager
111	100	DC	CI	Packaging	USA	Finance	Finance Manager
456	100	DC	CI	Packaging	Singapore	IT	IT Manager

Here's what I want to achieve with RLS:

1. Users with the "View All?" flag set to "Y" should have access to all data.
2. Users with specific columns filled in should only see the corresponding data, except when the "Exclusion" column is filled.
3. Users should not be able to see their own records.
4. Users who do not meet any of these conditions should not see any data.

Your help would be much appreciated.

Many thanks,

IzBell

[Anonymous]

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

[IzBell]

Hi @Anonymous 

Thank you very much for your reply.

The formula is very close, but two users are not getting the expected results.

For a@x.com

• it picks up 3 records rather than 2.

• Expected result - it should not include Singapore.

For h@x.com

• it picks up 2 records rather than 5.

• Expected result - it should only exclude record where DC + Result + HR + HR Manager.

Kind regards,

IzBell

[v-aatheeque]

Hi @IzBell ,

For the above scenario for  a@x.com

• This was happening because your email has multiple access entries in the RLS table (e.g., one for Canada and one for USA). However, the previous rule was only picking one row instead of combining them.

h@x.com seeing 2 records instead of 5

• This user had several exclusion conditions like Team, Org Unit, Role, etc. Earlier, these were getting mixed together, which caused valid rows to be hidden.
• Ensure that each exclusion rule is treated separately data is only excluded if it matches all conditions from any one exclusion row, which is the correct behavior.

Hope this helps !

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

[v-aatheeque]

Hi @IzBell ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[v-aatheeque]

Hi @IzBell ,

Hi @nanacobsbs ,

Has your issue been resolved? If a community member's response addressed your query, please consider marking it as Accepted Answer and click Yes if you found it helpful.

If you have any further questions, feel free to reach out.
Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @IzBell 

just checking in on your query regarding Row-Level-Security-based-on-organisation-structure-multiple.

If his suggestion helped resolve the issue, please consider marking it as the Accepted Answer, as it can be useful to others who may face a similar situation.

Should you still need assistance or have further questions, feel free to reply with more details — we're here to help.

Thanks once again for being part of the Microsoft Fabric Community!