Dynamic Data Masking using Power BI field parameters

A simple idea to apply data masking based on conditions

Recently, while I was building a Power BI report, I came across the so-called OLS (Object Level Security), the way to control access to specific objects within a semantic model, such as tables, columns, and measures.

In the same context, I was thinking about how to use it to implement an efficient data masking of some kind. However, I was always reluctant to use it for that purpose because, in my opinion, it is not a very clean solution given that it raises errors inside the report instead of simply masking data.

So I was wondering about a workaround to handle such cases more elegantly and I ended up with this simple implementation using field parameters. In this article, I want to share this solution hoping for future Power BI improvements in such a scenario.

Disclaimer: even though it may seem like a precise workaround, this is NOT a real and secure solution to mask data inside Power BI.

Preliminary steps

For the following examples, I used the Contoso dataset provided by Microsoft which can be downloaded here. I used a simplified version (removing all the other tables):

Before going deep into the details of several cases, to implement this method, first, you must create an empty column in the table where the fields to show or hide are located. It can be also a column with only “-”. For the following cases, I just created this column in the table “Geography”:

Case 1: In the report, there is a table visual with several fields and a measure. When a specific condition is met, one of the fields must be hidden inside the table.

On the Power BI canvas, I just created the following simple table using “Geography” and “Sales”:

The behavior I would like to reproduce is the following: when the “TotalCost” of the selected countries is above 500 mln $, then I want to see the column “RegionCountryName”, otherwise I want it to be hidden.

First of all, let’s create the conditional measure based on the “TotalCost” value. Given the current context, the basic measure (i.e. the value to be evaluated) can be written like this:

Now, the conditional measure can be something like this:

Saying that if the “TotalCost” of the selected countries is above 500 mln $, then 1 else 0. The value 1 means that the field “RegionCountryName” must be visible in the table, otherwise it must be hidden.

Now it’s time to create the field parameter that will help us reach our goal. The field parameter must be built in this way:

It must contain the column we want to show and the corresponding empty one created as the first step. Using the flexibility of such a table, we need to manually add the column called “Flag_visibility” which will be our ace up our sleeve.

Now, along with these objects, we need just another one, another measure that will filter the field parameter based on the condition we set before. This measure can be written like this:

The measure says that when the value of the field “Flag_visiblity” is equal to the result of the conditional measure, then 1 (i.e. visible column) otherwise 0 (i.e. hidden column).

Now, how to use everything all together to make it work?

First of all, we just need to add the field parameter in the table instead of using the field “RegionCountryName”. At the beginning, you will obtain something like this:

Both the original and the empty columns are visible. The trick now to hide the column conditionally is the following: in the lateral filters panel related to the table, we need to add the field “Flag_visibility” and filter it by applying a TOPN using the measure “_Column_visibility” just like this:

The TOPN filter will act as a dynamic filter based on the condition we have defined before (to test it, put the field “Flag_visibility” and the measure “_Column_visibility” in a table and see what happens when you select different countries). What it does is select only the “Flag_visibility” value corresponding to the given condition, and the result is shown below:

As you can see, when the “TotalCost” is less than 500 mln $, the column “RegionCountryName” remains hidden, while when the value is above the threshold, the column is shown.

Case 2: In the report, there is a table visual with several fields and a measure. When specific users access the report, they must not see one of the fields inside the table.

Using the objects created above, this case can be solved with a simple RLS implementation. In other words, to apply this dynamic slicing, we can do the following steps:

1. Create a users table with the list of users (i.e. their e-mails) and a column “Flag_Visibility” that will contain information about the conditional visibility (1: visible column; 0: hidden column):

2. Create a relationship between this table and the field parameter (using the common “Flag_visibility” field. Important: the filter direction must go from the users table to the field parameter table.

In the example above, I turned the 1-to-many relationship into a many-to-many relationship (which can be considered as the general scenario) and set the direction to single (from “Users” to “Parameter — RegionCountryName”.

3. Create the role to filter the users table based on the access e-mail (i.e. using the USERPRINCIPALNAME function):

Now, given that the “Flag_visibility” column will be filtered automatically by Power BI (because of the relationship we have created before), there is no need to use the TOPN filter on the table. If you test the role, you will get something like this:

But, what if we have multiple users and multiple visibility conditions? See next!

Case 3: In the report, there is a table visual with several fields and a measure. When user A accesses the report, he must see one of the fields inside the table; (let’s say “field1”) when user B accesses the report, she must see one of the fields inside the table different from user A (let’s say “field2”); when user C accesses the report, he must see a combination of the above (let’s say “field1” and “field2”).

This case is similar to the one above and it can be solved with the classic RLS. But how to implement it when conditions are more than one?

Very simply: we need to act on the field parameter and modify it in this way (adding also the field “ContinentName” for the sake of this example):

As you can see, each user has a “cone of visibility” of columns. This is just a very simple example and can be extended with more users and visibility conditions. At this point, the users table (connected to the field parameter table) may be something like this:

With the same role created before, if we test it, we can obtain the following results:

Notice the case for “Other user”: it can be used as a trick to show only the total without letting them see more details than that.

The last case is a combination of those above.

Case 4: In the report, there is a table visual with several fields and a measure. When a special user A accesses the report, he must see one of the fields only if a certain condition is met (e.g. if another field is filtered).

Clearly, this is not a case where RLS can be used because it will only show or hide a column disregarding the second filter condition. How to implement this? It is possible to follow a hybrid approach.

Suppose to have the same users table as in the previous case but without the “Flag_visibility” column. This table will contain only those users on which the specific conditional visibility must be applied: if a user is inside the list, then the second filter condition must be taken into account.

For the sake of this example, I added my e-mail and another fake user. Moreover, the second filter condition is the following: when the field “StoreType” (from the “Stores” table) is filtered, then the users in the list must see the column “RegionCountryName”, otherwise this latter column must remain hidden.

As in the first case discussed at the beginning, we need to create the condition measure that can be written as follows:

Such a measure combines both the condition given by the user contained in the list, and the filter on the field “StoreType”: when the user is not in the list, it means that he/she can see everything in the report without restrictions. Instead, when the user is inside the list and the field is filtered, the result is 1 (i.e. visible column), otherwise 0 (i.e. hidden column). As in the first case, this expression must be used in the filter measure “_Column_visibility”:

Now, we need to do the last steps: add the field parameter in the visual table and the column “Flag_visibility” in the filter panel filtered using the above measure and a TOPN:

At the beginning, with my e-mail, I have something like this:

The table does not show the field “RegionCountryName” (only the “-”) because the conditional measure is 0 (only the access control is TRUE i.e. my e-mail is in the list); but, when I apply a filter on the “StoreType” column, the conditional measure turns to 1 (both controls are TRUE), and the output is:

For other users who are not in the list, this latter will always be the case, despite the filter on the “StoreType” column.

Some condiderations

Despite the simplicity of this method, this is not a true data masking, even though the results resemble the case. However, this method seems to be clearer than the classic OLS applied with Tabular Editor on a Power BI semantic model: in that case, when a column is missing due to a security restriction, this happens:

The visuals are completely obscured with an error showing, even though the user can see other fields. Moreover, this has the same effect also on other visuals while the field parameter method will not lead to an error:

In all these cases, the method described in this article seems to be the solution for many scenarios. Notice that, you can apply this kind of filtering also on slicers (to show values based on some conditions) and measures (in this case, it is possible to add a condition in the measure itself to show the value of not).

Limitations

As stated before, this may be a very clever solution but it is not a true data masking solution. Some behaviors inside Power BI cannot be overcome, for example:

• If you give the users the possibility to filter the report using the filter panel on the right (for example, adding something in the section “Filters on this page” or “Filters on all pages”), it is not possible to add field parameters to it as simple columns, therefore, the conditional visibility cannot be applied there (instead, using OLS will mask data efficiently).
• If you have a slicer with a single selection, and you use a conditional measure to filter it, the selection will remain even though the other values will be gone. Unfortunately, the clearing slicer feature is something highly requested but not yet available in Power BI so, let’s hope for future upgrades :D

There may be some other aspects not covered here (e.g. performance in very complex and heavy reports with a massive amount of data) so, be aware of this!

Final words

This article is just to show a simple but very clean method to mask data based on conditions, even though some limitations are still present, making it not a real and secure solution. However, I wanted to share this because I think that this method may be the solution for some scenarios while hoping for new Power BI official features that will make this even easier to implement :D