Forum Discussion
RLS on multiple dimensions
I am stuck on the best way to configure RLS for my model. My company services healthcare facilities, each with their own patients and payers that must be kept confidential.
Facts
Charges
Payments
Has an inactive relationship with Charges when I want to show payments made on charges
Dims
Facilities
Payers
Patients
I initially defined RLS on the Facilities table as [Customer ID] = X, which would filter both fact tables.
My customers want a Patient slicer. Since the Patients table contains all patients from all customers and that would expose unaffiliated patients in the slicer, I used a measure as a visual filter on the slicer. The filter only allows the slicer to show patients that appear in the RLS-filtered fact table.
While that solves the immediate problem, I would like to apply RLS more rigorously in case the dim tables are accidentally exposed. What is the best way to define RLS in this situation? I considered the following scenarios:
Include Customer ID in the Payers and Patients table and extend the RLS DAX to filter all dim tables on Customer ID. I am leaning toward this.
Bidirectional relationships between the facts and dims. This doesn't work because it creates ambiguous relationships.
Facilities -> Charges -> Payers -> Payments
Facilities -> Payments
Thank you for your help in advance!
Hey kvnbn ,
The best approach is to apply RLS structurally at the tenant level rather than relying on slicer filters or bidirectional relationships. Add CustomerID (or FacilityID) to the Patients and Payers dimension tables and maintain a clean star schema with single-direction relationships flowing from dimensions to facts. Then define RLS on the top-level Customer/Facilities table (or via a user-to-facility mapping table if users can access multiple facilities). This ensures that Facilities, Patients, Payers, Charges, and Payments are all securely filtered through normal relationship propagation, prevents unaffiliated dimension members from appearing in slicers, avoids ambiguous relationships, and provides proper enterprise-grade multi-tenant security.
3 Replies
Hey kvnbn ,
The best approach is to apply RLS structurally at the tenant level rather than relying on slicer filters or bidirectional relationships. Add CustomerID (or FacilityID) to the Patients and Payers dimension tables and maintain a clean star schema with single-direction relationships flowing from dimensions to facts. Then define RLS on the top-level Customer/Facilities table (or via a user-to-facility mapping table if users can access multiple facilities). This ensures that Facilities, Patients, Payers, Charges, and Payments are all securely filtered through normal relationship propagation, prevents unaffiliated dimension members from appearing in slicers, avoids ambiguous relationships, and provides proper enterprise-grade multi-tenant security.
Hi kvnbn ,
why not apply the RLS of Patients and Payers Dimensions too?in one single RLS role you can have conditions on multiple dimensions.all these conditions will be applied to your fact table when RLS is enforced.
Please give kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
Hi kvnbn ,
Thanks for reaching out to Microsoft Fabric Community.Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out.
Thank you.
