Dynamic Row Level Security - Duplicates

[imranamikhan]

Hi everyone,

I have a generic Data Table with a list of customer IDs and company codes.

I have a Mapping Table of distinct company codes and associated users.

Using the unique company code in the Mapping Table, I have created a one-to-many-relationship with the Data Table.

I then created a calculated column to bring the associated User using a RELATED function.

I have then set up role level security so that users can see only company codes associated to them in all PowerBI reports. This is working as expected.

However, there will be scenarios where one company code will be associated to multiple users. If I create duplicate company codes in the Mapping Table per number of associated users, this will break the relationship between the Mapping Table and the Data Table.

Could anyone advise a better approach? I have tried following the suggestion below by creating a table which only contains unique company codes to act as a bridge between the Mapping Table and Data Table, but this has not worked for me.

https://nineboards.com/dynamic-row-level-security-in-power-bi/

best regards,

Ami

[imranamikhan · Accepted solution]

Thanks for the support all.

@MFelix  - I was testing RLS on the portal. However I followed the instructions in this blog and I have managed to achieve what I needed.

https://www.tallan.com/blog/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/

[aj1973]

Hi @imranamikhan 

Change this relationship to Both

[MFelix]

Hi @imranamikhan ,

Believe that the approach used in the link you provided is the correct one, however be aware that you need to have the filter for both sides  on the relationship between the company and the user/company table.

Where have you tried the RLS on the portal or to simulate it in the desktop version?