Dynamic RLS in Power BI

[balakiranatcbre]

Hello Everyone,

I am facing one challenge while implementing the Dynamic RLS in my project, please help with the scenario and data modelling.

Scenario with the below 3 tables involved,
1. Projects - This is the main table and acts as fact table having the ID coulmns, accounts info and base measures. Key columns are(ID)

2. Projects Share - This table having UserID & ParentID info, it is having relationship with ID column to the Projects table and also UserID can join the Account Default table which is the below 3 table. Key columns are (ParentID) & (UserID)

3. Default Account Team - This table having the User info including User name, email etc also what are Specific accounts added to the user. Key columns are(UserID)
Note: when I make the relationship between Projects Share & Default Account Table is having Many to Many relationship.

please help me to implement the Dynamic RLS in this datamodelling scenario.

Thanks,

Bala

[johnt75 · Accepted solution]

Create a new table like

RLS Table =
SUMMARIZE (
    'Default Account Team',
    'Default Account Team'[Email],
    'Default Account Team'[User ID]
)

And link this to the default account team. That should be one-to-many.

Many-to-many relationships are OK for the other tables, but make sure that they are single-direction, so that default account team filters project share and project share filters projects.

Create a security role and add a filter so that 'RLS Table'[Email] = USERPRINCIPLENAME().