I am new to this tool.We recently migrated to Power BI from 3rd party tool.We are trying to implement ROW Level Security for our scenario.We are getting Users(Total 9500 Users) Info from Azure AD. We have stores across all states in USA.We have to implement ROW Level security based on Store ID & Login Email ID.
Below 2 scenario's has to be implemented.
1)When user login to Power BI, Store table supposed to display data only for his/her store ID.
2)When any Manager logins, It is supposed to display all storeID's.
Our table Structures:
Store ID Store Name Storestate
100 ABC IL
101 DEF NY
102 GHI NJ
Store ID EMAIL
Appreciated your help.
You're nearly there.
- Create a relationship from your RLS_mapping table to the Store table based on Store ID. This will be created as Many-to-Many (which is ok) and with bidirectional filter (which is not). Change the filter direction to single, from RLS_Mapping to Store
- In the RLS_mapping table create a rule that says EMAIL=USERPRINCIPALNAME()
That's it. Now you can decide if you want to list the manager multiple times in the RLS_Mapping table (with all stores) or if you want to have a separate table that lists all manager emails and do a different rule there etc.
Appreciated your quick response.Instead of Joining both the tables,Can we Implemneted RLS secuiry like below in Power BI??
SELECT * FROM Store where storeID in
(select storeid from RLS_Mapping where Email=USERPRINCIPALNAME())
Whenever you test RLS for a particular role on Power BI Desktop you need to select both the role to test and the Other User role and you need to specify the email address of the test subject.
Thanks alot. After selecting both the roles it was working fine.I have to Incorporate my second Conditon into the logic.
When ever IS_Manager Flag Value ='Y' then show all storeID's.
When ever IS_Manager Flag Value='N' then Only show Assgined Store ID Data.
Store ID EMAIL IS_Manager?
100 email@example.com Y
101 firstname.lastname@example.org N
103 email@example.com N
We have total 9000 users,Out of which 3000 users has to see all records(no data level security).
As i mentioned in my previous reply based on the Flag it has to decide apply RLS or Show all records.
Check out the on demand sessions that are available now!
See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.
Features releasing from October 2020 through March 2021
Mark your calendars and join us for our next Power BI Dev Camp!.