Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply

Row Level Security using any source data base / file

Hi Guys, 

 

After scanning through the forums to find a way to implement Row Level Security using Power BI Report Server and not finding anything positive, I decided to build my own. I would like to share this with you all so others can overcome this challenge. 

 

Requirements

  1. Implement Row Level Security on a database based on A User belonging to a certain Department or Division or any other field that you want to filter the data based on
  2. Use Microsoft AD Login for authentication
  3. Reports to contain all data by default and only filter on user view. 

 

Assumptions 

  1. Users are only browsers on the Report Server and will not be editing the reports / dashboards 
  2. The User to Department mapping will be sotred in at able or file 

 

Design

  1. Create a Tables USER_ACCESS with the following 
  2. UID, Name, Role Type and Department / Division (or similar column)
  3. Populate the data in the table assigning users to a Department / Division
  4. Load this table in the Power BI Data Set and Join to the other table where Department / Division exists i.e. user_access.department = <fact_name>.department or <dimension_name>.department
  5. Cross Filter Direction = Both 
  6. Apply Security Filter in both direction = Yes 
  7. If you want to filter further objects based on this column join it to the USER_ACCESS table or the Target table you joined to and ensure the Cross Filter Direction is Single
  8. Reload the data and ensure the filter works
  9. On the Table USER_ACCESS create ameasure UserID_Match = IF(CONTAINS(USER_ACCESS,USER_ACCESS[FULL_USER_ID], USERNAME()), 1 , 0)
  10. When a user logs in their record will automatically return a value one. 
  11. Now add this Filter to the Visual Level Filter with a value set to 0. 
  12. Add the data along with the Department / Divion Columns on the Graph or Cross Table  
  13. The data should automatically filter to the user using the Report on the Power BI Desktop and Power BI Report Server. 

Disclaimer 

  1. This solution may not work for all depdneing on how your data joins. We have tested this on a Star Schema with 1 Fact and 9 Dimensions and it worked for all the Dimensions and Fact records. if you need help on this solution, please put your comments in below. 

Thanks to Greg King for helping me desig and test this. 

Good Luck. 

 

2 REPLIES 2
healthEteam
Resolver I
Resolver I

I believe that Row Level Security is a feature that is expected to be added in the January update (it was slated for November update but just now saw that it changed).  I may be looking at using your solution until this update as I am currently deploying a large solution using the Report Server and RLS is a requirement.

 

https://docs.microsoft.com/en-us/business-applications-release-notes/october18/intelligence-platform...

 

mohammadhijazi
New Member

Thank you @rajiv_tarafdar for sharing your solution, I have a question are you able to apply the same filter by current user to slicer?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors