Dynamic RLS for multiple PBIX files.

[ThoZe]

Hey guys. I'm trying to figure out an RLS design for my company. The organization has about twenty divisions—with several employees in each division.

We're using MariaDB as our database/data source, PowerBI Desktop to generate our reports, and PowerBi Service/app to disseminate said reports to our employees.

Is there any way to set up roles in a single PBIX file and have them carry over to other PBIX files through relationships? The goal is to not have to set up roles in each individual PBIX file. 

[v-janeyg-msft]

Hi, @ThoZe 

The above said is absolutely correct. RLS must be set for each different dataset. But you can put employees from different departments into different distribution groups, and then add the group in RLS, which is very convenient.

Compare groups - Microsoft 365 admin | Microsoft Docs

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ThoZe]

Thanks for the response. I just want to clarify the issue. My company will be using several datasets: monthly insurances, claims data, etc…

We're brainstorming security measures methods where each of our divisions and individual providers only get to see data pertaining to them.

I know we're going to use userprincipalname() in some form of dax expression, but my team is trying to figure out how to do this without having to manually create roles and assign emails to each dataset—as that will be highly arduous. 

Would you kindly point me in the right direction to streamline or "automate" this process?  

[v-janeyg-msft · Accepted solution]

Hi, @ThoZe 

Even if you use userprincipalname(), you still need to configure the role in the service, which is an impossible step to omit. 

What we can do is how to quickly and easily add roles, as I mentioned before, first set up different security groups or distribution groups for users according to their permissions, and then directly add groups according to different roles in the powerbi service.

Reference: Row-level security (RLS) with Power BI - Power BI | Microsoft Docs

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdbuchanan71]

If your network admins already have the security groups set up that contain the users for each division then you can assign the security group to the role and the RLS filters for that role will be applied to all users that are a member of the security group.

[jdbuchanan71]

Each model has it's own roles so you would need to combine them in a single model or replicate the RLS for each model.  A single model can pull views from multiple databases though.

[jdbuchanan71]

Further, if you have existing reports that you want to swap over to the new PowerBI dataset you can do that using the script in this post.

https://powerbi.tips/2020/05/hot-swap-power-bi-report-connections/

You can also write new measures in your model out on the service using the tabular editor and connecting to the xmla endpoint.

https://www.sqlbi.com/tools/tabular-editor/

https://docs.microsoft.com/en-us/power-bi/admin/service-premium-connect-tools

[jdbuchanan71]

@ThoZe 

If the data set for the reports is the same what you can do is set the model up in a single .pbix file.   This is where you would define the roles and write your measures.  Then you publish it to the service and use that data set as the source for your reports.

Your model .pbix does not have any reports built in it, it just houses the model that all your reports use so they all get the RLS from the single model.

[ThoZe]

What if the data sets are from different databases? Would views joined with other databases work?