RLS with multiple dimensions

[ruben_4HP]

Dear Power BI experts,

I'm running into an issue where I can't get my head around, completely stuck on this.

In my simplified model, there's a 'fact_sales' which is linked many-to-one with 'dim_department' and 'dim_product'. I also have two tables for dynamic RLS linked many-to-many with my dim tables, being 'rls_department' and 'rls_product'. Both those connections are bi-directional with security filters applied in both directions. 

The input for my rls tables is an Excel sheet composed as follows:

Department	Product	UPN
Department A	Product A	UPN A
Department B	Product A	UPN B
Department C	Product C	UPN C
	Product B	UPN A
	Product C	UPN A

This is split into my two rls tables in Power BI desktop:

rls_department

Department	UPN
Department A	UPN A
Department B	UPN B
Department C	UPN C

rls_product

Product	UPN
Product A	UPN A
Product A	UPN B
Product C	UPN C
Product B	UPN A
Product C	UPN A

I have defined one RLS role with the rules:

1. rls_department[UPN] = userprincipalname()

2. rls_product[UPN] = userprincipalname()

There are two possible scenarios in my security model

1. UPN B and C: User is allocated to both a department and a product > RLS is working 

2. UPN A: User is allocated to both a specific department and a product but also to another product. This user can see the following: department A from product C but all departments from product B and C. In this case my RLS is not working because it's too restrictive, it will filter department A and product A, B and C. Therefore, UPN A can't see the other departments of product B and C.

The goal is to setup dynamic RLS which is reflecting the business logic from the input Excel sheet.

I hope this clarifies my issue. If not, please let me know and I'll further explain.

Many thanks in advance for your time and help!

Ruben.

[lbendlin]

change your rules from 

rls_department[UPN] = userprincipalname() && rls_product[UPN] = userprincipalname()

to

rls_department[UPN] = userprincipalname() || rls_product[UPN] = userprincipalname()

[ruben_4HP]

Hi Ibendlin,

thanks for your time looking into this!

I'm not sure how I can implement this in my current model. Changing the role as suggested give me the following error (without further explanation):

Thank you very much in advance for your feedback.

Ruben.

[lbendlin]

please show a sample version of your data model.

[ruben_4HP]

Hi Ibendlin,

please find below:

https://optiprofit-my.sharepoint.com/:u:/g/personal/ruben_optiprofit_be/EWoC0ZOECTRHvks6dGx-5ykBfO6B...

Thanks!

Ruben.

[lbendlin]

Thank you for the sample data. That makes things so much clearer.

As I understand it now your logic will require that the hlp_rls table will need to directly impact the fact table via a composite key (department+product), and will need to contain all combinations of department and product explicitly. (So no option to say "see all products of that department).

At that point you will want to consider if RLS is really necessary for your scenario.

That's the end of my rope. Maybe someone else has a better idea.