Show the information but not the details: Power BI Data Masking

I wrote several row-level security articles so far, and a book. I explained how to restrict access to the data using row-level security approaches. However, one of the questions I got recently is this: “I want users to see one another’s sales information, but not to see who others are! How can I do that in Power BI?”

Doing something like above is a bit different from row-level security approaches. In this scenario, we do want the user to see other’s information, even how much sales they have made, But we do not want them to see WHO the others are.

This is more a data masking scenario than a row-level security. However, because the question often comes from the users of RLS, I explain it under that category. This blog post is about an approach for the explained scenario above.

Use Case: Comparison

One of the most common use cases for this work, is when you want users to compare their data with other users, but without knowing exactly who the other users are.

For example; let’s say in an organization, the goal for the reporting is that each sales person to see how other sales person’s are doing in the organization, so that they can compare their result and try to improve their outcome, but not to create a bad competitive environment with showing who is doing what.

Sample Dataset

I am using a very simple dataset with two tables; Sales Rep, and Sales Transactions;

This is NOT Row-Level Security

Before you go further in reading, I want to re-iterate that; this is not row-level security. In this scenario, we are showing the entire data rows to the users. However, part of the data is hidden from them, which are confidential columns.

If you wish not to show any data rows that is not related to this user, then I refer you to my row-level security articles about it;

Row Level Security in Power BI

Dynamic Row Level Security

Dynamic Row Level Security with Manager Level Access

Dynamic Row Level Security with Users and Profiles

Dynamic Row Level Security with Organizational Hierarchy

Row Level Security with Analysis Services Live Connection

Row level Security with Many to Many and Organizational Hierarchy

Calculating Totals in a row-level security implementation

Hide Confidential Columns

The first step towards this approach is to hide any column that you want the user to not see. for example; first name, last name, address, phone number, email address, etc.

In my example; I have hidden all the three columns below;

Add Masked Column

The second step is to add a column that you can use instead of the confidential columns. This column is a column that still represent a user in my table, but it doesn’t explain who that user is. I used the user ID to build this, you can use any other approaches for that.

Please note that, if the user ID is a known value (for example, personnel’s of your organization know others ID somehow) then you have to use other methods to mask it. random number can also work.

Create Visualizations with the Masked Column

Now that you have the masked column, you can use that to create a visualization. Because the confidentials columns are hidden, user won’t be able to see them anyway.

When I talk about the user, I mean a non-developer user. Someone who has the report viewer access, not the edit access on the dataset. This type of access can be provided when you use Power BI Apps, Workspace with View access, basic sharing, and any other methods with no edit access.

As you can see in the above presentation, when the user logs in to the report, he/she has no idea who the user 1, user 2, and user 3 are. The details are completely hidden from the user’s view. However, the user can see how much their sales transaction was.

Current User

Now the problem with the above implementation is that the user doesn’t even know what is his/her transaction. because everything is masked. Using a measure with the usage of UserPrincipalName function we can easily achieve that;

Current User =
if(
SUM('Sales Transactions'[Sales Amount])//only show the result when there is a transaction
&&
SELECTEDVALUE('Sales Rep'[Email])=USERPRINCIPALNAME(),
SELECTEDVALUE('Sales Rep'[Name])
)

This means that only reveals the name of the user for the logged in user.

Which then leads into having something like below in a visualization;

You can create other measures to show users’s email address, or other user’s confidential data using similar approaches.

You can see that I did some color formatting based on the current user. I did the approach which I explained here in detail. it is just a simple measure like below that is used for background color of the table columns;

Background color =
IF(not ISBLANK([Current User]),'light green','light grey')

Filter for current user only

The goal in this approach of course was to create a report showing everything. but if we want to just show this user’s data, we can add the Current User measure in the visual level filter, and setting that to not be blank.

No Row-Level Security Configuration is Needed

For implementing this approach, as you have seen already, no row-level security configuration is needed. You don’t need to create any roles, and don’t need to assign users to these roles. Because this is not row-level security. This method is just hiding confidential information from the user’s view.

Best Practice

I always recommend a proper data architecture solution for any implementation. In this scenario, because some of your users might be data visualizers with access to create reports using Power BI Desktop, I highly recommend using the shared dataset approach. If you follow that approach, users, even if the user Power BI Desktop, will only see the data after masking. Learn more about that approach here.

Summary

Data Masking is not row-level security. But it can uses some of the measures we use for the dynamic row-level security to reveal only part of the information that is not confidential.

The method explained in this article, can be expanded with some other variations depends on how many confidential data columns and tables needed to be hidden or how tables are related to each other.

Download Sample Power BI File

Download the sample Power BI report here:

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.