Forum Discussion
How to Set Up Row Level Security for 2 Separate Filter Conditions Without Duplicating Fact Table?
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.
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.
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?