cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Power BI Dynamic Row Level Security Suggestion

Hi All,

 

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:

 

Table1:Store:-

Store ID    Store Name Storestate

100              ABC               IL

101              DEF                NY

102              GHI                NJ

 

Table2: RLS_Mapping

   Store ID     EMAIL

    100            user1@live.com

    101            user2@live.com

    103             user3@live.com

 

 

Appreciated your help.

 

Thanks,

Raj

 

 

 

9 REPLIES 9
Highlighted
Super User II
Super User II

Re: Power BI Dynamic Row Level Security Suggestion

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.

Highlighted
Frequent Visitor

Re: Power BI Dynamic Row Level Security Suggestion

Lbendlin,

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())

 

Thanks,

Raj

Highlighted
Super User II
Super User II

Re: Power BI Dynamic Row Level Security Suggestion

sure, you could use LOOKUPVALUE() etc, but why?  Let the data model do the work.

Highlighted
Frequent Visitor

Re: Power BI Dynamic Row Level Security Suggestion

Lbendlin,

 

When i join 2 tables,RLS is not working when i tested with other user(User2@live.com(Power BI Pro).

 

reddy6400_0-1594758901461.png

 

reddy6400_1-1594759042105.png

 

 

Highlighted
Super User II
Super User II

Re: Power BI Dynamic Row Level Security Suggestion

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.

Highlighted
Frequent Visitor

Re: Power BI Dynamic Row Level Security Suggestion

lbendin,

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.

 

Table2: RLS_Mapping

Store ID EMAIL            IS_Manager?

100 user1@live.com          Y

101 user2@live.com          N

103 user3@live.com          N

 

 

Thank you,

Raj

Highlighted
Frequent Visitor

Re: Power BI Dynamic Row Level Security Suggestion

@lbendlin..Can you Please suggest?

Highlighted
Super User II
Super User II

Re: Power BI Dynamic Row Level Security Suggestion

Probably easier to create a separate role for managers, and simply not specify any RLS rules for that role.

Highlighted
Frequent Visitor

Re: Power BI Dynamic Row Level Security Suggestion

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Users online (732)