Circumvent cumulative behavior of RLS roles

Hi all

I built 2 reports, both connected to the same semantic model. But they each require a different method of RLS.

The model looks as follows:

• Clearance report 1 containing UserPrincipalNames of each user and separate rows for each office they’re allowed to access.
• Clearance report 2 containing UserPrincipalNames of each user and separate rows for each UserID they’re allowed to access.
• Dimension tables
• Fact table

Both these Clearance tables are related to the fact table through their respective dimension table. (see figure below)

I’ve set up 2 different roles applying RLS to each of the clearance tables.

For the sake of simplicity, let’s state that both roles return a completely different set of rows for a user. In reality there would be some overlap, but I don’t think that’s relevant for this case.

I initially thought that I would be able to select which role to use for each report in the service, but that doesn’t seem to be possible.
Since all users belong to both security roles and security roles are cumulative, the dataset returns all rows the user would have access to. That means that for report 1, it also includes the rows returned from the Clearance report 2 table, which is not what I intended it to do.

Can anyone think of a solution to my problem?