In my previous article/video, I explained how you can hide sensitive data from the user’s view. The method I explained there is a simple method, however, for a specific type of user. If you have users who are using Power BI Desktop to create reports on top of your model, and they are going to create DAX measures, then there is a better and more secure way. In this article, I am explaining that method.
Data Masking
If you haven’t read my previous article about the data masking, I just briefly explain that concept here:
Sometimes, you want to show all the information to all the users, but you want to hide the sensitive data, such as other user’s names, emails, any other sensitive information. In this article I explained a simple method about that;
Which Type of the User
The method mentioned above, is good for end-users, and everyone who uses either Power BI service or Power BI Desktop just to create visualization on the report, with no edit access to the dataset.
However, if you are dealing with a type of user who wants to create DAX measures from the Power BI Desktop using Get Data from the Power BI dataset, there would be a problem. Hidden columns and tables are not hidden from the DAX expressions. This means if a user can create a measure (even report level measure), that user has access to those columns.
Hidden columns and tables are not hidden from the DAX expressions. This means if a user can create a measure (even report level measure), that user has access to those columns.
In below, I have used Get data from Power BI dataset option, and you can see that the hidden columns are not visible in the list of fields. However, when I want to write a report level measure, they are accessible!
If you have this type of user, then it means there is a security breach, because user can write a measure and get the data columns that they did not supposed to see!
Row-Level Security
What is the solution? how you can really secure the data from even that type of the user? the answer is with row-level security.
I wrote several row-level security articles so far, and a book. If you are looking for some links to learn more, here are some;
Row Level Security in Power BI
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
The problem in the implementation of the data masking, can be solved using RLS. Here is a solution for it step by step.
Create a Copy of the Table with the Confidential Data (Not in DAX)
The first step is to create a copy of the table with the confidential data using a method which is not DAX calculated table. Why not DAX calculated tables? because we are going to remove the columns with the confidential data in it in the next step, and if the columns are removed, then DAX calculated tables won’t be able to get them anyways.
I use Power Query to create a duplicate of that table;
The duplicated copy, I renamed it as Current User Table (you can call it anything, this table would be hidden later on);
Remove the Confidential Data Columns
Now that this table has all the confidential data, You can remove the confidential columns from the source table; Sales Rep table in this case.
This table has now one column only, the ID, because it is needed to join to the sales transaction table. Now you can load the data into Power BI.
Remove the Relationship
This newly created table “Current User Table” should be disconnected from the rest of the model. Remove the automatic relationship (if created), and then hide the table from report view.
Setup RLS for the Current User Table
The “Current User Table” is the table that we are going to apply the row-level security on it, because this is the table with the confidential data, and user should only see their own data from it.
The RLS logic would be a very simple dynamic RLS filter as below;
Now this table would be only showing one row of the data for every user logged in.
DAX Measure for the Confidential Data
There are many different ways that you can write the DAX measure, here is one of those to get the Name of the sales rep;
Current User =
if(
SUM('Sales Transactions'[Sales Amount])//only show the result when there is a transaction
,
CALCULATE(
if(COUNTROWS('Current User Table')=1,FIRSTNONBLANK('Current User Table'[Name],0)),
TREATAS(VALUES('Sales Rep'[ID]),'Current User Table'[ID])
)
)
This would lead a visualization like below;
This, of course, shows the confidential data (current user’ column) of all users. However, that is expected, because this is the view of the user with Edit access.
Publish and Set up RLS in the Service
The last piece of the implementation is to publish your solution to the Power BI service (or Report server if you are using PBIRS), and then set up the RLS on the dataset in the service. This article explains the details of it;
Assign users to the roles. I’d suggest create an Office365 group for everyone and add that group here because users will see only their own information. for my sample implementation thought, I just assigned my other user to it.
The User’s View
The user’s view would be exactly as expected, They would see all the data, but only their own confidential data;
Even a pro user (a user who can create report level measure, with no edit access to the dataset), won’t be able to access the data of others, because that table is secured and filtered by the RLS logic
Alternative Method; Single-Directional Relationship
There is a part of the implementation, which you can change, and keep the relationship between the “Current User Table”, and the “Sales Rep”. But that relationship needs to be single-directional, towards the “Current User Table”, otherwise, this would filter the entire sales data, which is not what we want.
However, note that this relationship comes with pros and cons. The benefit would be, you probably won’t need many DAX expressions for every confidential data field, and you can un-hide the “Current User Table”. The disadvantage would be; sometimes this relationship won’t work with other relationships because of the way that it filters the data.
Summary: Which Method
In summary, the two methods of data masking I explained in this post and the previous one, has their own pros and cons.
The previous method, which works without RLS is only working for users who are not going to create report level measures (mainly report visualizers or end users). That method, however, is very simple to implement.
The method I explained in this post that uses RLS, is more secure of course, and it works for the scenarios that even users are able to create report level measure (but not for users with the edit access on the dataset). However, it is more complex to implement. even reading this post itself, showed the process is longer than the previous one.
I hope you enjoyed reading it, and enjoy the solution too. If you have any questions, please feel free to write it down in the comments below.
Download Sample Power BI File
Download the sample Power BI report here:
Video
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.