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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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;

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;

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:

Enter Your Email to download the file (required)

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

2 thoughts on “Show the information but not the details: Power BI Data Masking

  • Dear Reza, Thank you for the post. My question is whether there is any way as a Power BI consultant to restrict access to the pbix file for a customer so as to prevent to hand out the complete data model development I as a Power BI consultant developed to the customer? I am aware that it is possible in PowerBI.com to prevent end users from downloading the pbix file. However, in a case where the client decides to use an own Power BI report server (as they do not wish to distribute the reports via the Microsoft cloud) I as a Power BI consultant would need to develop the whole model on-premise at the client and the client (at least the IT-admin) would have access to the pbix file as it would reside on-premise. Thus my question whether there is any way for the PBI consultant to develop the model on his own premises and e.g. publish the reports to the customers on-premise PBI cloud? Thank you in advance. Best regards Marc

    • Hi Marc
      If you give edit access to the report, then the user would be able to see the model and change it.
      if you want to protect your model, one way is to host it in AAS, and give the live connection Power BI report to the user
      Cheers
      Reza

Leave a Reply

%d bloggers like this: