Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi everyone,
I am trying to apply multiple RLS rules from a single user table (dimension) to the same fact table.
My fact table contains data about customers - it shows who acquired the customer and who currently manages the customer. As a result, a single customer record in the fact table can show 2 different user ID's if the current manager of that customer is not the person who acquired it.
See below the data in the tables and their relationships:
factCustomers
customer_id | acquired_by_user_id | managed_by_user_id |
1242 | 1 | 1 |
1154 | 2 | 2 |
1178 | 1 | 2 |
1201 | 3 | 3 |
dimUsers
user_id | name |
1 | Person 1 |
2 | Person 2 |
3 | Person 3 |
dimUsers - factCustomers
user_id 1:* acquired_by_user_id (active)
user_id 1:* managed_by_user_id (inactive)
I can obviously use the USERELATIONSHIP function when doing COUNTROWS to calculate the no. customers managed by the user, however that will throw the following error when RLS is applied for the viewer:
Count of Acquired =
COUNTROWS ( factCustomers )
Count of Managed =
CALCULATE (
[Count of Acquired],
USERELATIONSHIP ( dimUsers[user_id], factCustomers[managed_by_user_id] )
)
I have 2 immediate solutions, although they both aren't ideal:
1. Duplicate the fact table to ensure 2 active relationships, so that different rows are returned in the 2 fact tables in accordance with RLS - I would prefer to avoid this if possible given that the dataset will grow in size.
Count of Managed =
COUNTROWS ( factCustomersManagedByUser )
2. Apply RLS on the factCustomer table with OR condition - this still shows some data that the user shouldn't see.
Is there an easier and more effective way to achieve this? I have come across similar posts regarding RLS, although the scenarios are slightly different to this.
Any help is much appreciated.
Hi @tasmitaros ,
I am not sure whether you've found the solution or not but here's the solution you would be able to use.
Step 1: Inactivate relationship between these two tables
Step 2: Change your formulas as below:
Count of Acquired = CALCULATE(COUNTROWS(FCTCustomers), TREATAS(VALUES(DIMUsers[user_id]),FCTCustomers[acquired_by_user_id]))
Count of Managed = CALCULATE(COUNTROWS(FCTCustomers), TREATAS(VALUES(DIMUsers[user_id]),FCTCustomers[managed_by_user_id]))
This should solve the issue:
However, The only issue with this approach would be the inactive relationship between these tables. if you do not have many formulas that you'd be writing this approach will work for you and if you do have more formulas then try putting this into Calculation Group.
Thanks
Hi @tasmitaros ,
You can refer to the following documents that may be helpful to you:
powerbi - RLS filter with multiple values assigned? - Stack Overflow
RLS with TWO Fact tables in Power BI - YouTube
Efficient RLS comes down to good model design. Star architecture is a great choice.
Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Learn
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-tangjie-msft,
Thanks for the reply, although I'm not sure this directly answers my question. With regards to the resources provided:
I'd like to know if it's possible for RLS to show all records in the fact table where any of the multiple foreign keys that relate to the user table are present WITHOUT showing figures for other users when the measures are present in a visual - refer to the final screenshot.
I understand this may not be possible, although I would like to know this definitively so I can determine which option is most suitable for my business' requirements. It would also be good to know if there are any proposed changes to RLS functionality in the future.
Thanks
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
85 | |
82 | |
66 | |
49 |
User | Count |
---|---|
137 | |
111 | |
101 | |
66 | |
65 |