RLS on Multiple Tables not behaving additive.

[JosephDodd]

Using the star method, our FACT table is revenue based. We are adding a DIM table of Opportunities from SFDC. Some have associated revenue, but most do not.

The expected result is to have a user log in, see all revenue for all accounts (regardless of whether they have an associated opportunity), and see all Opportunities (regardless of existance of associated revenue). Additionally they should be able to see revenue that is associated with opportunities. 

The issue is that when we add our RLS rule for our SFDC Opportunity table, it restricts user visibility to only sales that are related to an Opportunity. 

Here is our 1st RLS:

LOOKUPVALUE ( 'SECURITY SALESREP'[Security Salesrep], 'SECURITY SALESREP'[User Id], USERPRINCIPALNAME (), 'SECURITY SALESREP'[Security Salesrep], 'SALESREP'[Salesrep Number]

It's using whatever is on the SECURITY SALESREP table by User ID and giving access to corresponding records on SALESREP which in turn is filtering the SALES table. This works.

Additionally the relationship between the SALES table and the SFDC Opp table is many to many and one directional. This ensures that the end user will be able to see records on SFDC Opp that do not have any records on the SALES table. However, this gives individuals access to ALL records on SFDC Opp, when we only want them to see their own records.

Here's our 2nd RLS that breaks everything. After this is added to the same RLS Role on the SFDC Opportunity table, it completely restricts the users visibility to only sales that have related SFDC Opportunities.

 LOOKUPVALUE ( 'SECURITY SALESREP'[Security Salesrep], 'SECURITY SALESREP'[User Id], USERPRINCIPALNAME (), 'SECURITY SALESREP'[Security Salesrep], 'SFDC Opportunity'[RepNumber] )

Again, the intention is to restrict the user's access to just their own Opportunities while maintaining the users visibility to all sales and all their own opportunities regardless of sales.  

Shouldn't the 2 RLS rules on 2 tables on the same role, be addative, rather than reductive?

[parry2k]

@JosephDodd quick question, why do you have cross filter direction set to both between the sales person and combined table?

[JosephDodd]

I use columns from the SALESREP table as display values and slicers, and they need to be able to take filters from other tables in the model (Through the SALES table) to remove non-relevant values.

[parry2k]

@JosephDodd if you check official documentation, it is additive:

Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Learn

[JosephDodd]

I understand that the documentation says it is additive. However the combination of the two RLS rules are acting reductively in my model, not additively.