Dynamic RLS with many to many relationship

[Anonymous]

Hi, I would like to set up a Dynamic RLS with many to many relationship. Any advise would be greatly appreciated!

User access list 

User Email	Market	Species
John@gmail.com	All	Swine
Patrick@gmail.com	Thailand	Ruminants
Patrick@gmail.com	Malaysia	Poultry
Elise@gmail.com	Thailand	All
Elise@gmail.com	Japan	Swine

Data Set:

Market	Species	Sales
Thailand	Swine	100
Thailand	Ruminants	86
Thailand	Poultry	45
Malaysia	Swine	12
Malaysia	Ruminants	57
Malaysia	Poultry	46
Japan	Swine	31
Japan	Ruminants	256
Japan	Poultry	384

[Anonymous]

If the RLS is set up correctly, each person's view should be as follows:

John's View
Market	Species	Sales
Thailand	Swine	100
Malaysia	Swine	12
Japan	Swine	31
Patrick's View
Market	Species	Sales
Thailand	Ruminants	86
Malaysia	Poultry	46
Elise's View
Market	Species	Sales
Thailand	Swine	100
Thailand	Ruminants	86
Thailand	Poultry	45
Japan	Swine	31

[Anonymous · Accepted solution]

Hi @Anonymous 

This is my idea. You may give it a try!

1. Transform the UserAccessList table into the following structure: Replace "All" with all possible markets and species, then combine market and species to create a new key column in both UserAccessList table and Sales table. (To learn about how to transform the data with Power Query, you can download my sample .pbix file attached at bottom of this reply.) 

2. Create a many-to-many relationship between Sales table and UserAccessList table, set up the cross-filter direction to Single (UserAccessList filters Sales).

3. Create RLS role and set DAX rule on UserEmail column. Use UserPrincipalName() function for dynamic RLS. 

4. Publish the report to Power BI Service, go to Security page of the semantic model to add users to the corresponding role. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Anonymous]

Thank you Jingzhan,

May I know what is the purpose of having DimMarket and DimSpecies tables? Do they serve any purpose, given the filtering is flowing from the UserAccessList through the many to many relationship?

Additionally, I am setting up this access for over a 100 users, 30 countries and 20 species. For some users, they only need filter to either market, or species data. The list is going to be very long if I have to list down all countries/species combinations.

[Anonymous]

Please check this, modified model and RLS rule: (.pbix is attached at bottom)

In Power Query Editor, DimMarket and DimSpecies still exist but not loaded to the model. Personlly I recommend maintaining DimMarket and DimSpecies tables separately. But if you don't want to have them, we can also generate distinct value lists from UserAccessList or Sales directly for transformation use. It's up to you.  

Best Regards,
Jing

[Anonymous]

Hi @Anonymous   

DimMarket and DimSpecies tables are not a must. The main reason I added them was because I was worried that the Market or Species in the Sales table and the UserAccessList table would be incomplete. I have DimMarket and DimSpecies tables for providing values to replace "All" string in UserAccessList table. I use some Power Query functions to do that so you don't need to list down all countries/species combinations manually. Only rows with "All" will be expanded. 

In addition, the DimUser table is not a must either. You can remove it and create RLS rule on UserAccessList table directly. 

Check my transformation steps in Power Query Editor. I don't paste them all here to save the space. Let me know if you have futher queries. 

Best Regards,
Jing