#PowerBI #Microsoft #PowerBIDesktop #RowlevelFiltering #RowlevelSecurity #Security #PowerBIService #DynamicFiltering #DynamicSecurity
This is the continuation of the topic “Row Level Security”.
For Previous Blog post on Row-Level Security Click here
Why Dynamic Row Level Security is important? In the previous topic we have seen, how easy it is to implement the Static Row Level Security, but when it comes to huge datasets Static Row Level Security will have lot of administrative work to manage! We might have to create hundreds of roles. What can be done? This is where Dynamic row level security comes into picture!
We can use 2 simple DAX functions UserName() or UserPrinicipalName() to create Dynamic Row Level Security. In this post I will show you an example of Dynamic Row Level Security using UserPrinicipalName() function in #PowerBI.
For this, I am using my own data. I have two simple Tables namely
1. Project Information
2. Project Users
Project Information has information of the Projects that are being Executed, Start, Finish & Cost of the Project. And it is quite obvious that each Project is handled by a Project Manager.
In the second Table i.e. Project User we have information about all the Project Managers. You can see the screenshot of the table that I am using.
If you have AutoDetect Relationships ON, then it will automatically create the relationships between the Tables using Project Managers as the key.
To create Dynamic Row Level Security, I am using a sample Report that I have used in the previous article as well. In the screenshot you can see the Visuals Shadow feature, that is a new update released in May you can use it effectively for Look and Feel of your Report!
In the Report screenshot below, you can see each Project has a Project Manager. The main reason for this Report is, to show that, each Project Manager can see only his data rows from all visualizations.
In the Project User table, I am creating a DAX measure using UserPrinicipalName() function.
Now let’s add the measure to the Report visualization, you can see it in the below screenshot!
If we do not setup Row Level Security in the Report and when it is published to Power BI Service, it won`t slice or filter the data but it will only give you the username.
For that we need to setup a Row Level Security in Power BI Desktop before publishing the Report. I will filter each role based on their Emails with DAX UserPrincipalName() function. To create security, go to Modelling tab>Manage Roles. Create a Role and name it Dynamic or appropriate. And define a filter on Project User table as below using Emails field!
This filter means that logged in user, will only see his/her records in the whole data set. The Emails field in Project Users Table defined as usernames/emails of Power BI service. Now Publish the Report to the Power BI Service.
These are my published Report and dataset.
Now in the Power BI Service assign the users to the Security of the dataset.
Once you select on the Security it will take you to the Row Level Security, you can see the roles created for the Report. Add the users into the roles and share the Report to the user!
Note that adding a user here does not mean that they will see data in the Report. Remember that this Security is dynamic, it shows their data rows ONLY if the underlying dataset has a record for their username, and they will only see data rows related to their username, not others.
Now if other users open the Report, and if their usernames match one of the entries in Project Users Table, they can see their names, and data rows related to that in the Report.
You can see in the below screenshot Anil Kumar is viewing only his projects information in the Report.
You have seen how easy is to use Dynamic Row Level Security in Power BI using DAX UserName() or UserPrincipalName() function. With these functions, users will view the Report.
This is it! Try implementing this in your organization. And also, please comment, share and follow #PowerBIService
Comments