Dynamic RLS with filtering tables

[arif_ali]

Hello,

I have apply RLS on multiple tables and could not figure it out. Any assistance would be much appreciated. Here is the scenario (see the image)

Table 1 (Login User): I have stored user principal name and access level (1, 2, 3). 1 = access to own records, 2 = multiple products, 3 = full access

Table 2 (RLS): This is has each user multiple times depending  how many product he/she has access to (example: abc user is 3 times since she has access to 3 different products

Table 3 (Customers). Each customer is assigned 1 product. there is a column here called user. Only level 1 users are assigned in this column

I would like to filter table 3 based on the following criteria:

1. If user has level 1 access, should see only his/her customers from table 3. RPM field in table 3

2. If user has level 2 access, should see customers which are assigned based on table 2 (RLS)

3. If user has level 3 access, can see all customers regardless of products, region etc (no filter required).

I hope I have explained it well. 

Thank you,

[Greg_Deckler]

@arif_ali So normally you set your RLS rule on a single table. If you are using dynamic security like it seems you are, you limit users to see the row correspoding to themselves in a user table and let the relationships flow from there.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Hierarchical-Row-Level-Security/m-p/...

You may get quirkly results with many-to-many what is causing that? Can you create a composite key? Can you create a bridge table? What is the relationship built on right now?

[amitchandak]

@arif_ali , This many to many is a bit of concern. The approach should be like employee hierarchy RLS

See if these can help

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies

[arif_ali]

Hello,

I have looked at your YouTube and this one did not work because:

1. I have some users who have multiple profiles (i.e. can see 2 or 3 or 4 products)

2. Some users aer super users who can see all

I have created the bride table called (RLS) which as security set that user 1 can see 4 different products so that user would be 4 times in that table.

I would like to filter table 3 based on 3 different level of access. How else would you recommend to structure it?

Thank you,

Arif

[amitchandak]

@arif_ali , I think we need to try it. If possible

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[arif_ali]

Hello,

I have attached the screenshot of my sample pbix but for reasons, it does not allow me to attach pbix file. I would like to filter customer table based on the following criteria:

1. If user has level 1 access, customer table should be filter based on User

2. If user has level 2 access, customer table should filter using "Path" or "Pathcontain" functions (i.e. level 2 users should only see customers under his/her user territory).

3. If user has level 3 access, customer table should filter based on Geo (somehow using RLS table).

I cannot change customer and transaction tables but open for any ideas to redesign User and RLS tables.

Thank you,

Arif