Dynamic RLS based on sub group

[unnijoy]

I have a table based on Manager's. Its from M1 till M4. Now i need to create an RLS basd on this. Below is the table 

M1	M2	M3	M4
Alex	George	Lilly	Jeena
Alex	George	Shane	Rose
Alex	Rex	Glen	Jasmine
Alex	Tom	Green	Lotus
Alex	Tom	Peter	Red
Alex	Grace	Max	Gilly
Alex	Grace	Hexter	Jelly

Below is the Goal i need to achive.
1. People in M1 should be able to see the data off all people in M1 to M4.

2. People in M2 should see only the data of them and their reportee. Than is, George shoud see the data of George's overall, Lilly, Shane Jeena and Rose.And Lilly (M3) should see data of Jeena (M4). And Jeena (M4) should see only Jeena's Data only.
But in this their will be some people who may have too see the overall Data. For example Alex(M1), Tom(M2), Glen(M3). This peole should see overall data.

How can i achive this.

[v-tejrama]

Hi @unnijoy ,

Thank you for reaching out to the Microsoft Fabric Community Forum.

I’ve carefully reviewed your scenario and reproduced it in Power BI Desktop using the hierarchy structure and dynamic row-level security (RLS) logic you described.

Summary of Scenario Reproduction:

• Created a dataset with M1 to M4 hierarchy.
• Built an EmployeeData table where each row is owned by the M4-level person (PathUser).
• Added a UserAccess table to define user roles and full access exceptions.
• Created a flattened UserHierarchyMapping table to map each user to their direct and indirect reportees (M4-level employees).
• Configured RLS using a relationship and a DAX expression

I’ve attached the .pbix file and screen shorts used in this test for your reference.

You can open it in Power BI Desktop, change usernames via “View As Role”, and see the security in action.

Thank you ,

Tejaswi.

[unnijoy]

@v-tejrama , thanks for your help. I try the file that you gave. In that the isue is that lets take Max who is M3. so he should see only his data and the people who report to him. but when i try i can see that the Name his M1 , M2  is also coming. Actually it should not be the case. as he don't have oeverall access. then he should see only his name and the people who are reporting to him. 
And other thing is that i needd this M1 to M4 in filter. so in that case what is the relationship we need to create.

[v-tejrama]

Hello @unnijoy ,

Thank you again for the detailed feedback. you're right in your observation.

In the current model, since Max has access to Gilly’s data (as her M3), the full row including M1, M2, M3, and M4 is visible. Power BI RLS restricts row-level access, but it does not blank individual column values in that row.

This is why Alex (M1) and Grace (M2) are still shown they belong to that row which Max is authorized to view.

Consider below points:

• We can create secure versions of M1–M3 using DAX that hide those names unless the current user is authorized (or)
• If you want to display only the hierarchy (M1 to M4) relevant to the logged-in user, you can achieve this by leveraging the existing UserHierarchyMapping table. First, create a relationship between UserHierarchyMapping[Employee] and HierarchyTable[M4]. This links each user to their respective hierarchy row. Then, apply a visual-level filter or slicer on the HierarchyTable using the condition [Manager] = USERNAME(). This ensures that only those hierarchy rows where the logged-in user is the manager) are shown in the report. As a result, users will only see the M1 to M4 combinations that belong to their reporting tree.

Thank you.

[unnijoy]

@v-tejrama Thanks for the explanation. Here in our case. we should only show the data that the user is authorized to see. So if we are not adding All access. then they shouse see only the people who are reporting to them. In the above explantion you said that we can hide that using a DAX. can you pls show how that DAX will be.

[v-tejrama]

Hi @unnijoy ,

Thanks again for your clarification and follow-up.

I’ve now reproduced the scenario exactly as per your latest requirement where users should see only the data they are authorized to view, and all higher-level hierarchy values (M1–M3) are masked if not authorized.

In this updated solution:

• If a user is not granted full access, they will see only:
• Their own data
• Data of users reporting directly or indirectly under them (based on the hierarchy path)
• M1, M2, and M3 columns are now dynamically masked using DAX measures, so unauthorized users cannot see manager names above them in the hierarchy.
• Users with IsFullAccess = TRUE (like Alex, Tom) will see the entire dataset.

I’ve attached the .pbix file and screenshots for your reference. You can use “View As” to simulate different users like Grace, Max, Gilly, etc., and validate how the secure hierarchy behaves across levels.

Th@unnijoy,ank you.

[unnijoy]

@v-tejrama thank you very much. We are almost their. I need to use the M1 to M4 as a drop down filter. But in this case when i use M1 to M4 as filter and then view role as Red i can see all the name from M1 to M4 . How can we set so that only the people how they have access should come in the filter.

So as per my case only the name Red should come here. as he is M4 and no one report to him. how can i set that. Rest all are good.

[v-tejrama]

Hi @unnijoy,

Thanks again for your detailed feedback and scenario clarification.

I’ve now fully implemented the expected solution including:

• Dynamic Row-Level Security (RLS)
• Masked Hierarchy Columns (M1–M3)
• Secure Dropdown Slicers that show only authorized values per user

Users without full access now:

• See only rows where they are in M1–M4 path
• See only M1/M2/M3 names that they are authorized to view
• Have restricted slicer options using secure filtering measures (e.g: M1_Filter_Visible, M2_Filter_Visible, etc.)
  
  

I tested this using “View As”, and the output matches your expectation e.g: when viewing as Red, only Red’s data appears, and slicers show only relevant hierarchy names.

I’ve attached the .pbix file for your reference. Feel free to test with different roles like Max, Grace, or Gilly using “View As”.

Thank you.

Tejaswi.

[unnijoy]

@v-tejrama , thanks for the help. One last question. 

If i have some user's  who are not part for M1 to M4. But i need to give them access to see the sales.

Below are some of the cases.

Name       Access
Nithin        M1
Ullas          M1 M2 M3

Vicky          All

The above users are not part of any of this M1 to M4. But i need to give them access. how can we achive this.

[v-tejrama]

Hi again @unnijoy ,

yes, this can be achieved. For users like Nithin, Ullas, and Vicky, who are not part of the main M1–M4 hierarchy but still require access, we can handle them by extending the logic in the existing security model:

• We can add them to the UserAccess table with a custom field like AccessScope to define which levels (e.g: M1, M2, M3) they should have visibility into.
• Then, we adjust the secure masking DAX measures (like M1_Secure_Measure, M2_Secure_Measure) to honor this scope.
• Users like Vicky, who need full access, can be granted it by setting IsFullAccess = TRUE, and they will see all data without restrictions.

This approach keeps your RLS intact while allowing non-hierarchy users to securely view only the relevant parts of the report.

 
Thank you.

[Sandip_Palit]

You can solve this by first transforming your "wide" hierarchy (M1, M2, M3 columns) into a proper parent-child structure that DAX can understand. Then, you can apply a single, powerful DAX rule that handles all of your conditions.

Step 1: Create a Parent-Child Hierarchy Table
Your current data format is difficult for DAX hierarchy functions. The first and most important step is to create a proper parent-child table that defines who reports to whom.

In Power BI, go to the Home tab and select Enter data.

Create a table with two columns: Employee and Manager.

Manually enter the direct reporting relationships based on your M1 to M4 structure. For example:

George reports to Alex

Lilly reports to George

Shane reports to George

...and so on.

Name this table Employee Hierarchy and click Load.

Step 2: Build the Hierarchy Path Column
Now, we'll use a DAX calculated column to find the full reporting chain for every employee. This is the key that makes the dynamic security possible.

Go to the Data view in Power BI and select your Employee Hierarchy table.

From the Table tools ribbon, click New column.

Enter the following DAX formula. The PATH function traces the reporting line up to the top level.

Path = PATH('Employee Hierarchy'[Employee], 'Employee Hierarchy'[Manager])
This will create a new column containing text like "Alex|George|Lilly|Jeena".

Step 3: Create the Dynamic RLS Rule
Finally, we'll create the RLS rule that incorporates all of your conditions.

Go to the Modeling tab and click Manage roles. Create a new role (e.g., "Managerial View").

Select the table you want to apply the filter to (your main fact table) and enter the following DAX expression.

Note: This assumes you have a column in your main data table with the employee names/emails, here called 'YourFactTable'[Employee Name]. You must also create a relationship from 'Employee Hierarchy'[Employee] to 'YourFactTable'[Employee Name].

// Get the name/email of the person viewing the report
VAR LoggedInUser = USERPRINCIPALNAME()

// List of specific people who should see everything, regardless of their position
VAR OverrideList = { "tom@yourcompany.com", "glen@yourcompany.com" }

// Find the hierarchy path for the person viewing the report
VAR UserPath =
LOOKUPVALUE (
'Employee Hierarchy'[Path],
'Employee Hierarchy'[Employee Email], // Assumes an email column in your hierarchy table
LoggedInUser
)

// Condition 1: Is the user on the special override list?
VAR hasFullAccess = LoggedInUser IN OverrideList

// Condition 2: Is the user an M1? (People at the top of a path)
// We check if the user's name is the same as the first item in their own path.
VAR isTopLevel = PATHITEM(UserPath, 1) = LoggedInUser

// Condition 3: Is the employee in the row a subordinate of the logged-in user?
VAR isInHierarchy = PATHCONTAINS('Employee Hierarchy'[Path], LoggedInUser)

RETURN
// Show the row if ANY of these conditions are true
hasFullAccess
|| isTopLevel
|| isInHierarchy

If this explanation and solution resolve your issue, please like and accept the solution.