Forum Discussion
Dynamic RLS with many to many relationship
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.
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