RLS Filtering with Multiple Roles

[mchenwt]

I have a bit of a strange scenario that I'm not sure how to resolve.

I have four tables that are being filtered like this: Job Security->Job Table<->Department Table<-Department Security Table.

There are two RLS roles: one for job security and another for department security. These roles are just for filtering to the user's security via username(). I have attached some sample data below -- I apologize, I'd like to upload the sample pbix instead but cannot due to policy.

Job Security:

Job Code	Username
123	abc@d.com
123	def@g.com
456	def@g.com
789	efg@h.com

Job Table:

Job Code	Department Code	Job Name
123	DEPT1	Job Name 1
456	DEPT2	Job Name 2
789	DEPT3	Job Name 3
910	DEPT4	Job Name 4

Department Table:

Department Code	Department Name
DEPT1	Dept Name 1
DEPT2	Dept Name 2
DEPT3	Dept Name 3
DEPT4	Dept Name 4

Department Security:

Department Code	Username
DEPT1	123@d.com
DEPT2	def@g.com
DEPT3	hij@k.com
DEPT4	abc@d.com

RLS Security:

[Username] == USERNAME() in the Department Security Table for one role and the Job Security Table for the other role.

The issue that I'm running into is that for the visuals/pages with only department data, I want to only show the department data that is being filtered by the department security table, not via the job security>job table>department pipeline. An example of this problem can be seen by running RLS with both roles on username abc@d.com, where this user is able to see DEPT1 and DEPT4 and the goal is instead for them to only be able to see DEPT4.

Logically, it would make sense to make department->job a one way relationship instead of both ways, but the reason I can't do this is due to department/department security data being unfiltered in the job security RLS, thus allowing anyone with the job security RLS role able to view all department data.

I have also tried adding a second part to the job security RLS role to filter for the user's own department, but this causes it to only look for jobs that have them listed as having both job and department access.

I looked around on the web and found an idea to create a measure that would determine whether the row was coming from the department security table or the job security>job>department pipeline, and then use the measure as a filter on each visual to only look for rows coming in from the department security table. The measure is below (modified to work with sample data):

OwnDept = IF(VALUES('Department Table'[Department Code]) in CALCULATETABLE(
VALUES('Department Security'[Department Code]),
SUMMARIZE(
'Department Security',
'Department Security'[Username]
),
'Department Security'[Username]=USERNAME()
) , 0, 1)

But this only seems to work if that user has access to only one department. I think I understand that the reason this doesn't work since it can try to look for multiple values in a list of multiple values rather than one value in a list of multiple values.

Ideally I'd like to compare on a row by row basis which calculated columns seem to be able to do, but I am unable to use username() in a calculated column.

Does anyone have any other ideas or a way to make measures work in this particular scenario? I think I might be overthinking it but I have no clue how to proceed. Honestly, the only thing I can think of is working around it by just creating two separate dashboards which is unideal.

[v-heq-msft]

Hi @mchenwt ,
Based on the information you provided, I performed the following steps:

Create RLS

Final output

Best regards,

Albert He

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

[mchenwt]

This will not work. As stated, I cannot have it have both on the same RLS role because this limits the job table as well. I need it so that it will only limit department data and not job data.