In my last post, I explored a less-than-ideal way to approach the lack of object-level security in Power BI. That workaround used DAX to mask sensitive values for certain users. Since that method is not a secure one, I’ll look at an alternative that involves reshaping the data to make use of Power BI’s existing row-level security.
Please take the time to vote for column-level security in Power BI: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15578172-column-level-security-for-reports
Let’s consider the same conditions from the last post:
1) an organization wants users to view financial measures (Net Sales and Volume) while selectively restricting another measure for certain users (Margin)
2) a single version of the report is required — we cannot have multiple versions where sensitive columns are omitted from one and not another
3) users will access a deployed version of the report and not have access to the PBIX file
An alternate method to work around the current lack of object-level security in Power BI could be to unpivot your table so that the column values are now placed on rows. Once unpivoted, you can apply standard row-level security to prohibit users from viewing the sensitive data. The end result in the report is the same as masking values, but this second approach is more secure.
It’s not always ideal to unpivot a table, but if the situation is right, here is a simple setup. The original table might hold Margin, Net Sales, and Volume in separate columns.
Unpivoting the table puts the values on rows.
From there, it’s a matter of applying a common row-level security pattern. Use a security table to define what users can view. In this case, I want a certain user to see Volume and Net Sales but be restricted from viewing Margin. Then, setup a role or roles to appropriately filter based on the values in the security table.
I then add explicit measures that filter the unpivoted table to the correct records and aggregate the Value field appropriately.
The end result is the same as using DAX to mask values, but this workaround offers a more secure option until object-level security becomes available in Power BI (assuming it eventually does). It’s not always as viable to unpivot a table as it is in this simple example, but if you can get the data into the right format, you can leverage Power BI’s existing row-level security to get the result you want.
