cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heroyo
Helper I
Helper I

Managing a large number of reports with RLS

I have a report and would like to limit my users data access using RLS so they can only see the data that pertaints to them. I have about 75 differenct users that will all have their own specific data sets.

 

I am relatively new to PBI and RLS is a new concept for me.  I've read some tutorials and it seems fairly straight forward. I create roles in PBI desktop, upload to a workspace, and then give access by email address. I have a few questions:

 

I only have one pro account. Do all users have to have one?

Is there a way assign RLS based on a table so it is easier to manage?

 

Any other suggestions or references are greatly appreciated. Thanks!

1 ACCEPTED SOLUTION

Hi @heroyo -

  • You can create a table in your data source where you can maintain the mapping of the required security as seen below in screenshot

Sumanth_23_0-1619558546110.png

  • Create a single security role in Power BI report using [UserID] = USERPRINCIPALNAME()

Sumanth_23_1-1619558735032.png

  • Ensure you incorporate the Security table in your data model so it filter the main DIM table based on the security defined in the Security table 

Sumanth_23_2-1619558869148.png

  • Assign access to members to this single role in the service - this will ensure that folks see data based on the access setup in the Security table

 

Hope this helps! 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



View solution in original post

6 REPLIES 6
heroyo
Helper I
Helper I

Thanks @selimovd, I wish I could use the groups but I have 75 sales poeple and we don't want of them to see each other's performance. 

 

Is it theoretically possible to create one group per person? It would be a lot to implement but it is the only solution I could think of other than creating 75 individule reports. Are there any other ways to approach this problem? 

selimovd
Community Champion
Community Champion

Hey @heroyo ,

 

yes, you can add a dynamic row level security, where every user just can see his own data.

For that you need a table with the user information, like an employee table with the email address of each employee.

This table you can filter on the USERPRINCIPALNAME(), what is the function to get the email address of the currently logged in user. Like this the user can only see his own data.

Check the following article on how to do that:

Dynamic Row Level Security with Profiles and Users in Power BI : Many-to-Many Relationship - RADACAD

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Hi @heroyo -

  • You can create a table in your data source where you can maintain the mapping of the required security as seen below in screenshot

Sumanth_23_0-1619558546110.png

  • Create a single security role in Power BI report using [UserID] = USERPRINCIPALNAME()

Sumanth_23_1-1619558735032.png

  • Ensure you incorporate the Security table in your data model so it filter the main DIM table based on the security defined in the Security table 

Sumanth_23_2-1619558869148.png

  • Assign access to members to this single role in the service - this will ensure that folks see data based on the access setup in the Security table

 

Hope this helps! 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



View solution in original post

Thanks @Sumanth_23 this looks like an ideal solution. Would this work if I had only one premium account or would all my users have to have a pro account?

hi @heroyo - your users would also be required to have a Premium per User (PPU) license to access the reports that you publish - else if you have a Pro license then other users would also require a Pro license to view the reports

Below is the Microsoft documentation on the features for each of the license types: 

https://docs.microsoft.com/en-us/power-bi/fundamentals/service-features-license-type

 

License type Capabilities when workspace is in shared capacity Additional capabilities when workspace is in Premium capacity
Power BI (free) Access to content in My Workspace Consume content shared with them
Power BI Pro Publish content to other workspaces, share dashboards, subscribe to dashboards and reports, share with users who have a Pro license Distribute content to users who have free licenses
Power BI Premium Per User Publish content to other workspaces, share dashboards, subscribe to dashboards and reports, share with users who have a Premium Per User license Distribute content to users who have free and Pro licenses

 

Happy to help! 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



selimovd
Community Champion
Community Champion

Hello @heroyo ,

 

usually you would not give access by individual email addresses. You would create active directory groups and assign the users to the group. So you could create a group "UK", one for "US", one for "France" and one for "Germany" and assign the user by what they should see. Then you would create a role for people who should be allowed to see only "UK", one role for people who should see only "US" etc.

Then for each dataset you have to assign the corresponding ad group to each role. When you have then a new user responsible for "UK" you just add him to the ad-group and he automatically has access to the correct data.

 

If the people work together in a workspace they all need a pro account.

If you have premium capacity you can let the workspace run in premium capacity, people who only consume reports don't need an extra license, everyone creating a report still needs a pro license.

 

Also take a look at the REST API to script a few of the tasks, also for ad groups.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!