Forum Discussion
RLS Query - Power BI
Hello Microsoft Community,
I’m fairly new to Row-Level Security (RLS) and have successfully implemented RLS based on a hierarchy using a summary table that contains the hierarchy structure. So when a manager logs in using his/her email ID they can see the performance of all the employees under him using : PATHCONTAINS('Employee Table (RLS)'[Path],USERPRINCIPALNAME())
However, I now have a new requirement. I need to grant full access to all data for a specific manager who is not part of this hierarchy.
Is there a way to implement a “special role” rule in RLS that allows this manager to view all records based on their email ID, while still maintaining the existing hierarchy-based RLS for other users?
Any guidance or examples would be greatly appreciated.
Thank you in advance!
You can extend your existing rule with an OR condition for the users who should have full access.
PATHCONTAINS ( 'Employee Table (RLS)'[Path], USERPRINCIPALNAME() ) || USERPRINCIPALNAME() = "[email protected]"Or simply create a new role and add those people who will have full access withount bounding to the role with RLS.
9 Replies
Hello,
you can create a role in which you do not inject any filter, so just give it a name and you can assign this role to the manager through his e-mail
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Create one role with this DAX filter:
[Email] = USERPRINCIPALNAME() ||
PATHCONTAINS('Employee Table (RLS)'[Path], USERPRINCIPALNAME())Put the special manager's email in your security table with full access flag or just list it first. This gives them OR logic - either hierarchy match OR full access.
You can extend your existing rule with an OR condition for the users who should have full access.
PATHCONTAINS ( 'Employee Table (RLS)'[Path], USERPRINCIPALNAME() ) || USERPRINCIPALNAME() = "[email protected]"Or simply create a new role and add those people who will have full access withount bounding to the role with RLS.
Hello cengizhanarslan ,
Adding this email ID to the DAX formula in the formula was indeed the easiest to do.
Thank you all!
Hi RishabSajith
Here's further information since you're just getting started with RLS.
Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Learn
--------------------------------
I hope this helps, please give kudos and mark as solved if it does!
Connect with me on LinkedIn.
Subscribe to my YouTube channel for Fabric/Power Platform related content!
Try this:
IF ( USERPRINCIPALNAME() = "[email protected]", TRUE(), PATHCONTAINS ( 'Employee Table (RLS)'[Path], USERPRINCIPALNAME() ) ) or IF ( --if there is a table of managers to exempt USERPRINCIPALNAME() IN VALUES ( ExemptedManagersTable[manager email] ) TRUE(), PATHCONTAINS ( 'Employee Table (RLS)'[Path], USERPRINCIPALNAME() ) )Hi RishabSajith ,
I would also take a moment to thank danextian , cengizhanarslan and wardy912 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I hope the issue has been resolved. If you continue to experience any problems, please reach out here and we will assist you.Best Regards,
Community Support Team
Hello,
Im sorry, I'm new to the whole concept of Roles and View Accesses. So, am I supposed to go to Manage Roles and create a new role with nothing in it?
thanks,
Exactly, that will make everything visible, supposing this is what you were asking
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
