I am hoping find help to my current issue. I have got to implement a Dynamic RLS for hierachy over multiple columns of a dimension table.
The requirement consist of implementing an hierachy restriction as follow
TaxiDriveDax_0-1700151870049.png
Besides, an employee/manager can have access to a top level of the hierachy and have his staff accessing only few part of a level (including the belonging part of the below level). => hope this illustraction is clear enough.
However, I am having to deal with the blisted below tables:
RLS user_Group Table
Bridge Table Hi All,
I am hoping find help to my current issue. I have got to implement a Dynamic RLS for hierachy over multiple columns of a dimension table.
The requirement consist of implementing an hierachy restriction as follow
TaxiDriveDax_0-1700151870049.png
Besides, an employee/manager can have access to a top level of the hierachy and have his staff accessing only few part of a level (including the belonging part of the below level). => hope this illustration is clear enough.
However, I am having to deal with the listed below tables:
RLS user_Group Table
Bridge Table Bridge table
Dimension table Organisation Dimension Table
Thank you for your help
a general approach you can take:
Define the Hierarchy in the Dimension Table:
Make sure your dimension table contains the necessary hierarchy columns. In your case, it seems to be the columns "EmployeeID," "ManagerID," and "GrandManagerID" (as per the image names you provided). Create Relationships:
Create relationships between the hierarchy columns in the Dimension Table. This helps in navigating the hierarchy. Create RLS user_Group Table:
In your RLS user_Group Table, define the access levels for each user or group based on the hierarchy. You might have columns like "UserID," "AccessLevel," "EmployeeID," etc. Create Bridge Table:
The Bridge Table is used to link the RLS user_Group Table with the Dimension Table. It contains columns like "UserID," "EmployeeID," etc., to establish relationships. Define DAX Expressions for RLS:
Use DAX expressions in the RLS settings to filter data based on the user's access level and hierarchy. The DAX expressions will use the Relationship established in the Bridge Table. Here's an exemple DAX expression for RLS in Power BI: