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.
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;
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.
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.
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.
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: