Row Level Security using any source data base / file

[rajiv_tarafdar]

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. 

[healthEteam]

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]

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?