Power BI Row Level Security 2 – Dynamic Security

We have already looked at a very simple Static Row level solution example using two tables. Now We move to dynamic security

Here you create 1 rule only and the users are added to a table. Each logged in user will see their own data.

Dynamic Row Level security therefore uses a Users log in credentials to filter datasets.

Power BI Desktop Get Data

Open up Power BI and click on recent Sources because we are going to reuse the Adventureworks data

Select DimEmployee, FactResellerSales, DimProduct, DimProductCatgory and DimProductSubCategory (Import)

You can go to the relationships pane to view the relationships of the data brought in

For this example, every employee wants to be able to view their own information, but no one else’s.

The Email address in DimEmployee contains the email address of the Power BI account

For this example, we are going to give two employees email address that use Power BI within our own company. (Because obviously, we don’t have Adventureworks email addresses set up with our Power BI Service)

At this point it should be noted that I have Admin Access in the App Workspace that we are publishing to. This means that I have full access regardless of row level security

Choose two other team members with Power Bi Pro Access to add to the dataset in the next step. This will be set in the next step of the process

Power BI Desktop. Create New Measures

First of all, I am going to create two measures on the Employee Table. Click on … Against Employee in Fields and Select New Measure

Repeat for Employee Principal User = USERPRINCIPALNAME()

And a measure in the fact table Now = NOW()

What is the difference between USERNAME() and USERPRINCIPALNAME()

The USERNAME() DAX function returns the user’s domain login in the format domain\user) locally, but returns the user principal name (the user’s login credential) in the Power BI service.

Therefore, security role filter expressions, user permissions tables, and any other dynamic user functionality added to Power BI datasets should align with the UPN email address format provided by USERPRINCIPALNAME().

Power BI Power Query. Set up UserName

Click on Transform Data from Home

Go to Transform and select the email address column. Go to replace Values and replace linda3@adventure-works.com with another email address (I am using my own email address here)

Do the same with a couple more email addresses for the two test addresses (Internal email addresses of your choosing)

Once completed Close and Apply.

Power BI Desktop. Create Visualisations in Report

The report is specifically to show the dynamic row level security so I’m not setting up anything special

• Card: User (Measure above)
• Card: Now. (Go to Modelling and format to change the format of Now (As above))
• Table: Title, FirstName, MiddleName, LastName, EmailAddress, BirthDate, EmployeeNationalIDAlternateKey, MaritalStatus, Phone, SickLeaveHours, Status, Gender, VacationHours
• Column Chart: Axis – Order Date, Value – Sales Amount
• Pie Chart: Legend – EnglishProductSubcategory, Value – Sales Amount
• Card: Principal User (Again, measure created above)

Note the User Name shown in Desktop. Before we set up the roles lets simply publish what we have into the service

I will again Publish to Adventureworks demo. Click on the new report from the App Workspace you have chosen

Note now we are at service level my email address has become my user name, which now matches the Email Address in the Employees Table. My Email address is used to authenticate my Power BI account.

As an admin in the app workspace security doesn’t apply to this email address

Go back to Desktop

Set up Security

Modelling > Manage Roles

If you try viewing as Employee in Desktop you will see no data because within desktop, your email address isn’t used as your user name. Republish and replace your Report

Service

In Service, as before, go to Security by clicking … against the data set within the App workspace.

Add all the email addresses of members here. If it matches employee email they will see their information. Add all your email addresses as members above

Top tip. Obviously you don’t want to be adding single emails here so the best thing to do is to create an Azure AD Group and add all your users into this. They can then be managed centrally and we simply add the group here

I also add the other two users into Members. if you click … by Employee you can test as role

Note that now I can see my sales information and personal details and can only see my information. I can change to another email to see their details.

How does this effect Access in the App workspace and App?

There are two places to look at the reports. the App workspace where developers contribute to the work.

Access Levels Admin – Member – Contributor – Viewer (We dont tend use viewer within the app workspace)

AND the App where the reports are consumed by people in the business. the access level here is basically Viewer with a few possible add ons from contributor if required

If the email above isn’t in th App Workspace, the user cant access from here.

If the user hasn’t been added to the App they wont be able to view the report contents. Lets have a look at some examples

The Model

for Dynamic Security you must have a table with your user details (Email Address) The simplest design is to have this in a dimension that connects to the fact table

The more complex the table relationships you will have to check that your Security still works.

If your table is not joined to the DimEmployee table, you will see all the records.

If you have issues with your security look at your relationships and what tables are being used in your reports

Conclusion

The logic within this post answers one specific question. But what if we want certain users to be able to see everything? The next how to guide will go into more detail on how to set up users who can see everything