Country level security for users using RLS

Power BI Data Model
1

Problem:
I have parent dataset published in power BI service. There is “Store Person” table which has records with “StoreNo”, “Active_Directory_UPN”, “CountryCode”. I have another table called “Store” with “StoreNo”. I have used RLS based on email address of user which I am storing on “Active_Directory_UPN” field of “Active_Directory_UPN”. There is one way multi to multi join from “Store Person”.StoreNo to “Store”.StoreNo. Now I have some users who are attached to few stores but I need to provide them country summary data but they should not be able to see individual records for other stores. How can I solve this?

What I tried:
I created another dataset which connect to parent dataset. Added local “Store Person (Local)” and “Store (Local)” tables then connected them based on CountryCode and finally connected to parent “Store” table with StoreNo, but I could not remove RLS applied on Parent dataset from child dataset.

2

Hi @bharatsatyal - Please share a sample PBIX file.

Thanks
Ankit J

3

Parent.pbix (22.3 KB)
Child.pbix (28.5 KB)

Here Child report is directly connecting Parent dataset in Power BI. Child has separate security but uses all the measures and tables from Parent based on CountryCode that user belongs to.

It’s only working as expected in Power BI desktop, when I publish it it does not work.
I can’t modify Parent with countryCode security from person Store to Store as it impact security on parent. I only like to provide access to users who comes from Child report, if they view child report then they should have access to Country level data and can only access report not Child dataset. They don’t have access to dataset and sees report using app. When I publish files to PBI service Child reports breaks for 2 visuals.