Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
tasmitaros
Frequent Visitor

Apply multiple RLS filters on same fact table

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_idacquired_by_user_idmanaged_by_user_id
124211
115422
117812
120133

 

dimUsers

user_idname
1Person 1
2Person 2
3Person 3

 

dimUsers  -  factCustomers

user_id  1:*  acquired_by_user_id (active)

user_id 1:* managed_by_user_id (inactive)

 

PBIDesktop_6mW3XpVK3I.png

 

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] )
)

 

 

 

 

PBIDesktop_2JiRRy54D1.png

 

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.

 

96g1JL4QKh.png

 

 

 

 

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.

 

PBIDesktop_1CEmSHDcIC.png

 

mbsVvkHXUm.png

 

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.

3 REPLIES 3
Parthlad
Frequent Visitor

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

Parthlad_0-1690388291737.png

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:

Parthlad_1-1690388419262.png

 

 

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

v-tangjie-msft
Community Support
Community Support

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:

  1. The Stack Overflow topic is concerned with what is visible in another dimension table based on the pairs of user_id and app_id in the fact table - that's not quite what my case is.
  2. The RLS with 2 fact tables GIAC video is a good explanation, but again it doesn't answer my question.
  3. I am familiar with that RLS article from Microsoft and my model is designed in accordance with a star schema.

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.