Row-level Security on Measures in Power BI

Row-level security is important in order to ensure that only certain eyes can view reports with sensitive data. For example, if I were to create a finance report containing sensitive budget and revenue metrics, I may not want the entire company to be able to view and interact with the report. Power BI allows “row-level security” or RLS out of the box which allows you to limit the data shown to users based on user roles. This works by filtering a table down to specific rows that an individual is allowed to see. This is well documented, of course, but I wanted to see if you could use the same functionality in order to limit the measures that an individual can see.

As an example, let’s say that I create a report with 3 main calculations: cost, revenue, and quantity. I want the VP of the company to be able to see all 3 calculations in the report. I want the Finance team to only be able to see the cost and revenue information. And finally, the Warehouse team should only have access to view the quantity info. As it turns out, we can set this up very easily using RLS!

I’m going to illustrate two methods for how you can set this up, a dynamic method and a static method. The dynamic method will involve using a dynamic measure to change all of the calculations on the page depending on the measure selection and access level. The static page will have visuals with a static measure, such as quantity, revenue, or cost, and our RLS will simply show the visuals as blank where they don’t have access.

Dynamic Method:

As you can see, when I change roles to “Warehouse”, the metrics that I am allowed to view only show Quantity whereas the “Finance” role is able to view the Cost and Revenue calculations. I also added a “VP” role that is allowed to view all three calculations.

The steps to set this up are very easy. Firstly, create a separate measures table using the Enter Data option under the home tab.

I called the table “Measure Selection” and entered the three calculations that I want to choose from. These can be named anything you want.

Next, add a slicer with the column we just created on to the screen to allow for selection. This won’t do anything at this point.

At this point, we need to set up a dynamic measure using the SWITCH function. The whole code for my measure is as follows:

Selected Metric =

SWITCH(

    TRUE(),

    SELECTEDVALUE(‘Measure Selection'[Measure]) = “Cost”,SUM(Sales[Cost]),

    SELECTEDVALUE(‘Measure Selection'[Measure]) = “Revenue”,SUM(Sales[Revenue]),

    SELECTEDVALUE(‘Measure Selection'[Measure]) = “Quantity”,SUM(Sales[Quantity]),

    SUM(Sales[Cost])

)

We use a normal SWITCH function set to evaluate for TRUE(), and then we check if our Measure Selection slicer has a SELECTEDVALUE of “Cost”, if so return SUM of the cost column. If the slicer is set to “Revenue”, SUM the revenue column, and the same for quantity. Finally, the last line defaults the calculation to Cost in case there are multiple selections.

Throw this measure into every visual on the page! When you select Cost, for instance, every visual will now show cost.

Finally, we need to set up the RLS to finish the set up. Under the Modelling tab, select Manage roles. Create however many roles you would like, and filter the table you created like such:

You can see the for my Finance role, I have filtered my Measure Selection table down to where the column [Measure] = “Cost” OR [Measure] = “Revenue”. Now when the Finance team views the report, they will only see these two measures in the slicer. I created the same set up for the Warehouse role, but only included Quantity. Finally, I added a VP role with no restrictions.

Click Save. The button just to the right of Manage roles is View as. Select from any of your rows and your possible measure selections will change! (See previous GIF)

Static Method:

We can use our existing set up from the previous step and change only a couple of items. In this method, we are not going to use a single, dynamic measure. Instead, we are going to set up individual Cost, Revenue, and Quantity measures and place each calculation in a separate visual on the page. The logic of each measure is going to determine its visibility. I will simply add the code for my Cost measure and I’ll let you adapt it to any other calculations you need.

Cost =

IF(

    “Cost” IN VALUES(‘Measure Selection'[Measure]),

    SUM(Sales[Cost]),

    BLANK()

)

Here we are saying “if Cost is included in our list of allowed measure, return SUM of the cost column, else return blank.” We want to return blank when the measure is not allowed for the role to hide any data that might be shown.

That’s all we have to do! See the following GIF for how this works live.

I hope you enjoyed this blog post! If so, make sure you subscribe to the blog and the BI Elite YouTube channel! Happy Learning!