Dynamic RLS

[dr_anas]

Hi there, I have to create an RLS for my report, however I need some help and advice.

1. Because the data connected to Orecal is immutable, I am unable to alter the current fact tables to add a key identifier to create a relationship between the employee table and the related table.

2. The pages in my report are different.

3. To access certain visualizations, an RLS must be built for each employee email address.

As an example, please find screen grabs of the Fact table and RLS tables.

Please provide a hand

.

[hackcrr]

@dr_anas 

Since you cannot change the fact table directly, you need to create a bridge table that links your RLS table (which appears to contain employee information) to the fact table. This bridge table will use a common attribute, such as Email or Employee Number, which can be mapped to the RLS table and the relevant fact table.
This table should contain at least two columns:
Employee Number (or Email)
A key or identifier that can be linked to the fact table (e.g. ORG_CODE, DEPT_LOC, etc.)
You need to create relationships between your RLS table, bridge table, and fact table:
Link the RLS table to the bridge table:
Relationship: RLS[Employee Number](or Email) ->BridgeTable[Employee Number]
Link the bridge table to the fact table:
Relationship: (BridgeTable[Identifier] e.g., ORG_CODE)DEPT_LOC -> corresponding column in the fact table (e.g., FactPharmaSales[ORG_CODE]).
Create a role in PowerBI Desktop that will restrict data access based on your RLS table. Your DAX should look similar to:

[Work Email] = USERPRINCIPALNAME()

Then you need to test it according to your actual situation to ensure that it can run effectively.

If I have answered your question, please mark my reply as solution and kudos to this post, thank you!

[dr_anas]

Thank you @hackcrr.I'll try and let you know if it works.

[lbendlin]

Either in your RLS table or in another reference table you need to create a connection between Employee ID and CUSTOMER_CODE  (assuming that is your RLS goal)

 I have to create an RLS for my report

Are you sure?  Is the data really sensitive enough to warrant the huge extra effort and inconvenience?

[dr_anas]

Thanks, yes and that is why I am looking for support