Dynamic RLS based on sub group

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 = { "[email protected]", "[email protected]" }

// 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.

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.