We need to roll out a global dashboard where managers can only see data for their specific territory. I want to avoid creating 50 different roles manually. Is there a way to use the USERPRINCIPALNAME() function effectively alongside an Active Directory group mapping to make the security dynamic and easy to maintain as our team grows or changes?
3 answers
Just remember that RLS doesn't encrypt the data; it only filters it. Also, make sure your users are added to the "Viewer" role in the workspace for RLS to actually trigger.
Dynamic RLS is the way to go for your use case. You should create a mapping table in your data model that links the User's Email (matching their Power BI login) to the Territory ID. In the "Manage Roles" section, you would then write a DAX filter for your security table like [UserEmail] = USERPRINCIPALNAME(). By creating a relationship between this security table and your Fact table, the filters will automatically cascade. This means you only ever need one role in Power BI, and the data visibility is controlled entirely by your mapping table.
Do you have the ability to manage the mapping table through an external source like an Excel sheet in SharePoint or a SQL table? This makes it much easier for HR or Department heads to update access without needing a Power BI Developer to touch the PBIX file every time.
Thomas, that sounds efficient. If I use a SQL table for the mapping, do I need to set the storage mode to "Import" for the security table to work with RLS, or can I leave it in "DirectQuery" to ensure that any changes in access permissions are reflected immediately in the dashboard?
Good point, Susan. Many people forget that "Contributor" or "Member" roles bypass RLS entirely. Setting them as "Viewers" is the only way to ensure the DAX filters are applied.