How to Set Up Row Level Security for 2 Separate Filter Conditions Without Duplicating Fact Table?

[chasejc]

I'm trying to set up a RLS such that a salesman can switch between viewing sales he made personally to any customer and sales made to just customers he's responsible for which includes ones he didn't personally make, in the same dashboard.

My Sales Data is the fact table and Sales Rep and Customer are dim tables that connect to the main fact table. My initial attempt was to duplicate the Sales Rep dimension that connects directly to Customer so that the RSL can propagate off of that too, but that makes the resulting data be filtered by both Sales Rep tables. So even though in a table view of the Sales data I only have either the Sales Rep column from the original dimension or the duplicated one, the resulting Sales data is still filtered by both.

Is there a way to get this to work in 1 semantic model without duplicating the Sales fact table along with the Sales Rep dimension? or maybe use 2 different semantic models? Not sure if that's possible.

[danextian]

Hi @chasejc 

Row-Level Security (RLS) is enforced at the semantic layer, which means it is applied at the data model level and cannot be overridden or altered at the report layer. As a result, report-level filters or visuals cannot bypass or change the access restrictions defined by RLS.

When multiple roles with different levels of access are defined, a user who is assigned to more than one role will inherit the combined permissions of those roles. This determines the scope of data the user can access when interacting with reports that are built on the same semantic model.

What to try:

• @Zanqueta 's suggestion
• If different permissions are required depending on the view, consider creating a dedicated calculated summary table that exposes only aggregated values. The calculated table must be designed at the appropriate granularity, since different visuals or audiences may require aggregations at different levels (for example, by month, region, or product category). This table will not be affected by RLS as long as it does not receive filter context from any table that has RLS applied. For example, if the Employees table has RLS, it should not have any relationship with the summary table.

[chasejc]

If I were to create a summary table that doesn't have any RSL applied, how would I be able to only show information for a seller if they view it from their account? I'm not sure what the actual practical reason to have a summary table here is.

[v-saisrao-msft · Accepted solution]

Hi @chasejc,

Thank you @samratpbi @Zanqueta @danextian, for your insights.

Apply RLS to set the maximum access for each salesperson, so they can view either their own sales or sales made to customers they manage. RLS should define the complete range of data they are allowed to see and should not be used as a toggle. Create a disconnected table with options such as “Personal Sales” and “Customer Responsibility” to use as a slicer in the report. Depending on the slicer selection, use measures to adjust the visuals to display either personal sales or customer-owned sales, and apply these measures as visual-level filters (like > 0 or not blank) to manage what is shown without needing extra tables or relationships.

Thank you.

[Zanqueta]

Hi @chasejc  I am not completely certain, but based on what you described, the core issue is not a lack of semantic models, but rather the need to separate two different filtering paths for Row-Level Security without having those filters combine. This is a common requirement in sales scenarios, particularly when a salesperson needs to switch between:

Sales they personally executed for any customer
Sales made to customers for whom they are responsible, including those executed by others
The good news is that you do not need to duplicate the fact table, nor create two separate semantic models. The requirement is to separate the logic of filter propagation, and duplicating dimensions with active relationships will not achieve that.
 
Below are a possible approaches, with the first one being the recommended option.
 
RLS with USERNAME() and an Access Configuration Table
create an auxiliary table that stores the two types of access per salesperson. For example:
SalesRepAccess
-----------------------------------------
UserEmail | SalesRepID | CustomerID | AccessType
 
Where:
AccessType = "PERSONAL" filters by SalesRepID (sales executed by the user)
AccessType = "CUSTOMERS" filters by CustomerID (customers under the user’s responsibility)
A single user may have two rows with different access types. The user switches between these in the report using a slicer, rather than switching via RLS.
Your RLS rule becomes simply:
SalesRepAccess[UserEmail] = USERPRINCIPALNAME()
Then, in the report, you filter the fact table using a measure such as:
 
Sales Filter :=
VAR currentSelection =
SELECTEDVALUE(AccessSelector[AccessType])

RETURN
SWITCH(
currentSelection,
"PERSONAL", Sales[SalesRepID] IN VALUES(SalesRepAccess[SalesRepID]),
"CUSTOMERS", Sales[CustomerID] IN VALUES(SalesRepAccess[CustomerID])
)
This measure is then applied as a visual-level filter returning TRUE.
This approach works best because filter propagation happens through a single active relationship, and filtering is controlled through a measure rather than by duplicating relationships. 

[chasejc]

I'm not seeing how the Access table will solve the double filter problem. How are you linking the Access table to any table in my example in a way that gives you this kind of flexibility?

[samratpbi]

Hi @chasejc  some data example would have been better, however I think what you want is so see sales made by a salesman - 1st scenario, sales made to his customers -2nd scenario. Now the 2nd scenario will be larger dataset. Hence you can create a RLS based on 2nd scenario. Then have a filter / view on logged in salesman so that salesman can see their own sales only.

Hope this helps to resolve your problem. If it does, then please mark it as solution.

Thanks - Samrat