Customizationeble Organizational RLS Hierarchy

[4GoosE2]

Hi im having a big complex problem creating a Organizational RLS Hierarchy for a PowerBi report. Since I can not share any of the data ive asked ChatGPT to sum it up for me and maybe explaine it better then i could. Here is what he wrote.

"

The Problem

I have a company hierarchy where:

• Managers should see all employees under them.
• Employees should only see themselves and their direct teammates.
• Special cases:

1. Luke Skywalker & Leia should see each other's direct reports but not employees from other branches(/create these special connections for people without re writing the whole code.).
2. Higher managers (e.g., Yoda) should see everything.
3. Teammates should be able to see eachother

The Organizational Structure

UserID ParentID HierarchyKey

Yoda	NULL	/Yoda
Obi-Wan	Yoda	/Yoda/Obi-Wan
Darth Vader	Obi-Wan	/Yoda/Obi-Wan/DarthVader
Luke Skywalker	Obi-Wan	/Yoda/Obi-Wan/LukeSkywalker
Leia	Yoda	/Yoda/Leia
Darth Maul	Leia	/Yoda/Leia/DarthMaul
Kylo Ren	Leia	/Yoda/Leia/KyloRen
Mace Windu	Yoda	/Yoda/MaceWindu
Qui-Gon Jinn	Mace Windu	/Yoda/MaceWindu/QuiGonJinn
Anakin Skywalker	Mace Windu	/Yoda/MaceWindu/AnakinSkywalker

What I Want to Achieve with RLS Yoda sees everything (since he's the top manager). Each branch manager (Obi-Wan, Leia, Mace Windu) sees their own branch’s employees. Employees see only themselves and their direct teammates. Specially selected people can see each other's employees but NOT other branches.

"

I hope this explains it good enough, and if that isnt clear im trying to do this in a way that will have a low mantinance. Idealy only to add these special roles where some managers can see each others "Childern".

[v-ssriganesh]

Hi @4GoosE2,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @4GoosE2,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @4GoosE2,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @lbendlin for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[lbendlin · Accepted solution]

Use Dynamic RLS and an externally managed mapping table.

Managing hierarchical organizations in Power BI security roles - SQLBI

[4GoosE2]

Thank you i will look in to that!