RLS-> Dynamic RLS on hierachy for different colum of a dim table

[TaxiDriveDax]

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

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

• Dimestion table

Thank you for your help

[TaxiDriveDax]

Hi @123abc ,

Thank you for your proposition. I like to implemented it just that i still a bit more clarification as in if i should create a physical relationship between the Dim and the bridge table. also, you've not mentioned where to add the dax (which RLS table). then a confusing point is when you've stated i should use a calculated table to insert into my RLS choice. May you please guide toward which colum and table would carry the DAX.

[123abc]

The tables you mentioned are:

1. RLS user_Group Table
2. Bridge Table
3. Dimension Table

From your description, it seems like you want to implement a dynamic RLS based on a hierarchy, where an employee/manager can have access to a top level of the hierarchy, and their staff can access only a portion of a level and the belonging part of the below level.

Here is a general approach you can take:

1. 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).

2. Create Relationships:

   • Create relationships between the hierarchy columns in the Dimension Table. This helps in navigating the hierarchy.

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

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

5. 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 relationships established in the Bridge Table.

Here's an example DAX expression for RLS in Power BI:

=
CALCULATETABLE(
'YourDimensionTable',
USERELATIONSHIP('BridgeTable'[UserID], 'RLS user_Group Table'[UserID]),
'YourDimensionTable'[EmployeeID] IN VALUES('RLS user_Group Table'[EmployeeID])
|| 'YourDimensionTable'[ManagerID] IN VALUES('RLS user_Group Table'[EmployeeID])
|| 'YourDimensionTable'[GrandManagerID] IN VALUES('RLS user_Group Table'[EmployeeID])
)

This DAX expression checks if the user has access to the specified EmployeeID, ManagerID, or GrandManagerID based on the relationships established through the Bridge Table.

Remember to replace 'YourDimensionTable', 'BridgeTable', and 'RLS user_Group Table' with the actual names of your tables.

Adjust the DAX expression based on your specific hierarchy and table structures. It's important to test the RLS rules thoroughly to ensure they provide the desired level of security.

If you encounter specific issues or have more details to provide, feel free to share them for more targeted assistance.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.