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.
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.
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.
A pop up window will appear. Click on Create.
Then we need to enter the names. In this case, let’s call them North Sales Team and South Sales Team.
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.
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.
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.
You will then have this list of options. In this case, let us look at our North Sales team. Then let’s click OK.
Now, we can view all these visualizations filtered for the North Island.
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.
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.
We will then be asked if we want to save changes. Let’s click Save.
Then I’m going to put it in my own workspace.
We’ll then be informed that we have published our row level security in Power BI. We click on Got it.
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.
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.
After entering the email addresses, we can check if it’s working.
Click on a sales team and then click 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.
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.
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.
***** 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 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….