Apply multiple RLS filters on same fact table

[tasmitaros]

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.

[Parthlad]

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

[v-tangjie-msft]

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. 

[tasmitaros]

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