(Row Level Security) RLS via Manage Roles-could not filter data based on 2 security tables

[ss2025_01]

Hi,

I have to set up data filters for the tables in one of my dashboards (via Manage Roles). We have User Group and User Department. Until now we had to do RLS only on User Department. Now we have to do it on the User Group as well. We were applying RLS based on UserName on security tables of User Group (like security_dept[User Name] = USERNAME()) and the data tables (LOOKUPVALUE(security_dept[DepartmentId],security_dept[User Name], USERNAME(),security_dept[DepartmentId], Table_1[DepartmentId])) as well and it is working fine.

Now that there is User Department, we have to have the code that allows the user to see the data of both - the Departments and the Groups that the user has access to - RLS based on Group or Department. I tried many DAX solutions but nothing seems to work. Example of one of the solutions that didn't work is as below.

Data table : Table_1 (Department, DepartmentId, Group, GroupId, Name, Employee ID, Email, ....)
Applied RLS :
OR(Table_1[DepartmentId] = LOOKUPVALUE(security_dept[DepartmentId],security_dept[User Name],USERNAME(),security_group[DepartmentId], Table_1[DepartmentId]),
Table_1[GroupId] = LOOKUPVALUE(security_group[GroupId],security_group[User Name], USERNAME(),security_group[GroupId], Table_1[GroupId]))
Security table 1 : security_group (Group, GroupId, UserName) Applied RLS : security_group[User Name] = USERNAME()
Security table 2 : security_dept(Department, DepartmentId, UserName) Applied RLS : security_dept[User Name] = USERNAME()

When the above code was applied in Security of the dashboard, the user is not able to view their Group and only the data of the Departments they are given access are accesible. When Security table 1 filter was removed, they were able to see all the Departments' data and when Security table 2 data filter was removed, they were able to see all the Groups' data. It looked like Data table's filter did not even get applied. One more thing, there is no relationship between Group or Department.

Any ideas to get this fixed ?

[Poojara_D12]

Hi @ss2025_01 

To fix the issue with Row-Level Security (RLS) involving both User Group and User Department, the first step is to address the no relationship issue between these two fields in your model. Without a relationship, the RLS filters cannot apply correctly. You might need to create a bridge table or establish indirect relationships between Group and Department to enable proper filtering. Additionally, the DAX expression using OR might not be correctly applying the filter across both fields. It's better to use AND for combined conditions to ensure both filters are applied simultaneously. Testing with the View as Role feature in Power BI Desktop can help simulate the behavior of different access combinations, ensuring users see the correct data. Consider creating separate roles for each dimension (group or department) if necessary. Refining your DAX logic to apply both filters correctly will ensure that users only see the data they are allowed to based on either their group or department.

 

[ss2025_01]

Hi @Poojara_D12 , I tried your solution - part of it - I used "AND" instead of "OR" - it it shows the Departments that the user have access to and also the Groups they have access to but they are now able to see the data which has NULL for Department ID and Group ID. So, I will try to create the relationship for theose 2 security tables and see if that fixes the issue. Thanks.