Stopping Some Users Seeing Certain Columns Or Measures In Your Power BI Report With Object Level Security And Field Parameters

If you have sensitive data in your Power BI dataset you may need to stop some users seeing the data in certain columns or measures. There is only one way to achieve this: you have to use Object Level Security (OLS) in your dataset. It’s not enough to exclude those measures or columns from your reports or to hide them, because there will always be ways for enterprising users to see data they shouldn’t be allowed to see. However the problem with OLS up to now is that it didn’t play nicely with Power BI reports and so you had to create multiple versions of the same report for different security roles. The good news is that there’s now a way to create one report connected to a dataset with OLS and have it display different columns and measures to users with different permissions.

Let’s say you have a dataset and report that looks like this:

As you can see, it displays the names and addresses of employees along with sales and bonus data.

Now let’s say that the address and bonus data should only be visible to HR and everyone else should only be able to see the names and sales values. As I said, the only way to achieve this is to create a role that uses OLS to deny access to the address and bonus columns. Gilbert Quevauvilliers has a great post showing how to set up OLS using Tabular Editor here so I won’t go into detail about how to do this, but here’s how I configured the role in Tabular Editor 3:

If you publish the report and test the role in the browser, you’ll see that you get a “The visual has unrecognized fields” error because the table in the report uses the Address and Bonus fields which, of course, the user cannot access because of the OLS:

Security is working as expected but wouldn’t it be great if, instead of seeing an error here, you could build a single report that displays all the fields when the user has permission to see them and only displays the Name and Sales fields to users who are members of the role with OLS applied?

Well, now you can thanks to the new field parameters feature. The intended use of field parameters is to enable the end users of your reports to choose the fields displayed in visuals using a slicer. Behind the scenes when you create a field parameter a table is added to your dataset with one row for each field you have chosen; this effectively makes the fields used in a visual data-driven, and you can use Row Level Security (RLS) on the table created for your field parameter to control which fields are displayed in your visual and solve the problem.

Going back to our report, the next step is to create a new field parameter with all the columns and measures used in the table:

Notice that the “Add slicer to this page” checkbox is deselected because you don’t need a slicer on the report here. Here’s what the table created for the field parameter looks like:

With the field parameter created it can be used instead of the individual fields in the table definition:

You can then edit the role that already has OLS in it to apply RLS on the field parameter table, so only the rows for the fields that are allowed by the OLS are returned:

'FieldsForMyTable'[FieldsForMyTable] = "Name" ||
'FieldsForMyTable'[FieldsForMyTable] = "Sales"

It’s important that the OLS and RLS are defined in the same role because of the restriction on combining OLS and RLS from different roles.

With all this done, when you view the report through the role you only see Name and Sales displayed:

It’s important to stress that the OLS is still securing the data here – the RLS is just preventing the errors.

One downside of this technique is that things could get complicated if you have multiple visuals that need to display different combinations of secured and non-secured fields in a report. There could also be a performance penalty: when a visual uses a field parameter an extra DAX query is run on the field parameter table to determine the fields to display, and while these queries should be extremely fast most of the time there’s always a risk that they somehow slow your report down.

In conclusion, this workaround isn’t ideal but I think it’s the best way to work with OLS in Power BI reports that’s possible at the moment.

[Thanks to John Vulner for background information on how field parameters work]