Scale Your Power BI Reporting with Row Level Security

Implement Row Level Security In Your Power BI Models

No comments

Whenever I think of row level security in Power BI I think scale.

Within larger organisations, you often need to develop reporting that goes up the chain, but only certain levels should be able to see certain things.

To make sure people see only what they are allowed to see, you can create one model and use row level security instead of having to develop ten different reports.

This obviously works really well for security purposes. But it’s also useful for privacy. Well, row level security helps out massively in that regard as well.

In this post, I run through a really practical example of how row level security in Power BI can be implemented in your own environment.

I go through an example data set where we sell in different regions, but I want to make sure the correct filters are in place so teams can’t see results for other regions.

Reviewing The Model

Before I show you how to use row level security model, let us have a look at the regional information in the model that we’re going to use.

In this example, we’re going to analyze the total sales of two sales groups in New Zealand. These sales groups are represented by the islands. We have the North Island sales group and the South Island sales group.

sales group by islands with total sales

We also have three more visualizations. As you can see, we have Total Sales by Product Name, Total Sales by Customer Names and Total Sales by Date.

Visuals - Total Sales by Product Name Total Sales by Customer Names and Total Sales by Date

What we want to do is if we send out the sales information, only the North Island sales group will receive North Island data and only the South Island sales group will receive the South Island data.

Using Row Level Security In Power BI

Let us now move on to building the security model.

If you look at the top of your screen, you will see a security section. Click on Manage Roles.

manage roles

A pop up window will appear. Click on Create.

pop up where to click create

Then we need to enter the names. In this case, let’s call them North Sales Team and South Sales Team.

pop up where to name the roles

The next step would be to add a filter so that the value for the South Sales Team is South and the value for the North Sales Team is North.

In the image below, we are adding filter to the South Sales Team. So we click on Regions, then Add filter, and then Island.

filter by island

In the box intended for the Table filter DAX expression, we need to indicate which island we are working on.

Since we are working on the South Sales Team, we have indicated [Island] = “South”. After this we click on Save.

adding filter to south sales team

We repeat the process for the North Sales Team.

Check If The Security Model Works

Now that we have created our security model, we need to check if we have done it correctly.

To do this, we’ll go back to the security section on top. This time, we’re going to click View as Roles.

clicking view as roles

You will then have this list of options. In this case, let us look at our North Sales team. Then let’s click OK.

selecting to view as north sales team

Now, we can view all these visualizations filtered for the North Island.

checking north row level security in power bi

For the South Island, of course, we can do the same thing. And after going through the process, we’ll see these visualizations filtered for the South.

checking south row level security in power bi

So, we have just created a security model for our two sales teams.

But how do we send the reports to the right people?

Publish and manage security model

After setting up your security model, you will then have to publish and manage it in the online service.

To do this, we’re going to click on Publish located at the top of the screen.

step 1 of publishing row level security model in power bi

We will then be asked if we want to save changes. Let’s click Save.

step 2 of publishing row level security model in power bi

Then I’m going to put it in my own workspace.

step 3 of publishing row level security model in power bi

We’ll then be informed that we have published our row level security in Power BI. We click on Got it.

row level security model in power bi published

After publishing to the online service, we need to provision the people who will be receiving the report.

At the left side of the screen, let’s look for datasets. This is under my workspace since this is where I uploaded it.

Below datasets, look for row level security, click on the three dots, and then click on security.

giving provision to people

You will then see a box where you can enter the email addresses of the people you want to give provision to the report.

When doing this, however, you need to make sure you are giving access to the correct people.

In this case, the North Sales Team is selected so the emails we need to enter should be the addresses of those from the North Island.

box where to enter email addresses

After entering the email addresses, we can check if it’s working.

Click on a sales team and then click Test as role.

test as role

Since we are testing as someone from the South Sales Team, we will get a report that shows only the information of the South like what we have below.

how report will look like for South Sales Team

Again, you can test for those in the North Sales Team and you will be able to see the information provisioned for those in the North.

Conclusion

In this post, we have seen how to use row level security in Power BI to make sure that we have applied the correct filters so that information is sent only to the right people.

We have provisioned only two groups in this example, but of course, you are not limited to this. You can use row level security to send reports to every retail store if needed.

I highly recommend learning and understanding row level security really well. Make sure you know how to set it up from beginning to end.

You should think about the set up before you start developing anything, as it can certainly change the shape of your report pages quite significantly, depending on the filters you end up putting in place when the row level security does its work.

I hope you enjoyed this post.

Cheers,

Sam

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

 

 

membership banner 3

 

***** Related Links *****
How To Organize Your Power BI Models – Best Practice Tips
How The Data Model & DAX Work Together – (1.7) Ultimate Beginners Guide to DAX
Budgeting Performance Segmentation Using DAX In Power BI

 

***** Related Course Modules *****
Mastering DAX Calculations
Power BI Super Users Workshop
Advanced Data Transformations & Modeling

 

***** Related Support Forum Posts *****
DAX to ignore row level security
Row Level Security
Row Level Security and RANKX
For more row level security support queries to review see here….

 

Enterprise DNA Events

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.