Microsoft’s Power BI enables developers to easily display business insights in a way that is digestible to any user. Eliminating the need for technical knowhow to understand data analysis increases the accessibility factor for large businesses, in turn enabling improvements to be carried out from the individual to a company-wide scale. However, when showcasing data to a wider audience, it becomes necessary to protect pieces of that dataset from a security perspective.
Fortunately, Power BI has a few great ways to not only do this effectively, but also do it intuitively and in a way that matches your organizational structure.
Row-level Security (RLS) relies on a specific DAX function as the backbone of the whole security operation: USERPRINCIPALNAME(). This function enables Power BI to see which Microsoft email was used to view the report, and is generally used to only show data belonging to that user. Below, we have an example data source showing a standard employee table.
If we wanted to only show data belonging to Abigail, the RLS needed would be very minimal: all that is required is using USERPRINCIPALNAME() and requiring it to match the email column for the data. This would result in Abigail only seeing her own data, and no one else’s. To do this, we use the Manage Roles functionality in Power BI and put the DAX on the appropriate table, as shown below:
To propagate the security across the tables, we simply need to ensure a relationship exists between the tables in question. The common factor between Employees and Sales is the email with a one-to-many cardinality. For any amount of tables, as long as the relationship model is correct, the security will “flow” between tables, only showing data that matches the criteria we set in the Role.
Now, this will only work for an organization that only wants to show any individual their own data. What if we want to show data for a manager’s report chain? This is where the invaluable PATH() function comes into play. PATH() allows us to follow a chain upward using the Employee’s own ID and their Manager ID. We only need to create a calculated column with the following code, and we will get a path of IDs for each employee as shown below.
With a path for each user, we now can map to a manager at the top of the chain and show only the right people the right data. The next and final function we need to do this is PATHCONTAINS(), and this will end up showing data for someone if they exist in that path calculated column at all. The DAX used in this section is not as intuitive, but the logic is a lot simpler: if an employee is not a manager at all, the only path they appear in will be their own. Otherwise, a manager will appear somewhere in the path of any reports, direct or indirect.
So for example, if we are viewing the report as Abigail Johnson, any chain that has 1 in it will show up in the report. This will show data for all employees the end up reporting into her, as shown below. The RLS automatically excludes users 8-10 as they are not within Abigail’s report chain.
Summary
Enforcing security on data reports can often be very tedious and can very frequently result in holes in your model that could cause data leaks. Power BI has a well-built internal system in RLS that is perfect for generating business insights for an entire company while maintaining security measures perfectly. While this is a great approach for a basic organizational structure, reality can oftentimes introduce more complexities. If you have more questions on how to best implement security on your Power BI reports, please feel free to contact us!