Forum Discussion
Dynamic RLS with many to many relationship
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 |
| [email protected] | All | Swine |
| [email protected] | Thailand | Ruminants |
| [email protected] | Malaysia | Poultry |
| [email protected] | Thailand | All |
| [email protected] | 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 |
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!
5 Replies
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 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!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.