Forum Discussion
Dynamic RLS based on sub group
Hi unnijoy ,
Thanks again for your detailed clarification and the use case around additional users like Nithin, Ullas, and Vicky who require access even though they’re not part of the standard M1–M4 hierarchy.
I’ve reproduced your extended scenario as requested and implemented the following enhancements:
- Dynamic Row-Level Security (RLS) with secure masking for hierarchy levels (M1–M3)
- Access control for non-hierarchy users (like Nithin, Ullas, Vicky) using the IsFullAccess and AccessScope logic
- Secure dropdown slicers that respect visibility rules
- Masked hierarchy display using DAX measures (e.g: M1_Secure_Measure, M2_Secure_Measure, etc.)
Output Validation (Using “View As”):
- Users like Red, Gilly, Grace, Nithin, Ullas, and Vicky were tested.
- Each user only sees the rows they’re allowed to view.
- Slicer values are restricted based on their access level.
- Full access users like Vicky see the entire dataset.
For your reference, I’ve attached the updated .pbix file containing the complete logic, security setup, and working visuals.
Feel free to test it using "View As" → Other User for different usernames.
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.