With row-level security, data-based filtering can easily be added to a semantic model. By using the logged-in user’s username, this can also be made dynamic. Of course, much has already been written about this. Recently, however, I was asked to configure the security so that users at a higher level could always view all data, but at the detailed level, only the information pertaining to their own team or department. To clarify: At the top-level, users should be able to view all revenue, but at the detailed level, only customers in the Netherlands.
What is RLS?
Role-based security involves filtering data based on specific security rules. This is often implemented in Power BI by defining different roles. Data can then be filtered within these roles. Users are only allowed to view data from a specific company, a specific country, or a specific business unit. By making this security dependent on the user, maintenance and the number of roles can be significantly reduced.
Security Filtering Behavior
The way the model interprets security can be influenced by the “security filtering behavior” setting. This setting can be configured on a per-relationship basis. This can be automated using scripting, if desired. There are three basic settings available:
- One Direction
Records filtered on the “To” side of the relationship automatically filter the records on the “From” side - BothDirections
Filtering on either side of the relationship affects filtering on the other side. - None
No filtering takes place on either side of the relationship.
As an example, I used a model with dynamic filtering based on the customer's country. Row-level filtering is configured on the customer table. The test user has access only to customers in the Netherlands. This user is not allowed to view the other customers.
One Direction
The default security setting is “OneDirection.” This means that the customer table is filtered to show only customers in the Netherlands. The relationship runs from the fact table (revenue) to the customer. Therefore, the fact table is also filtered to show only customers from the Netherlands. Consequently, the revenue displayed in the various tables is limited to revenue from the Netherlands. The filtering applies only to relationships with the customer table; therefore, the slicer containing contact information is not filtered.
BothDirections
Using the “BothDirections” setting filters the tables on both sides of a relationship. The relationship between the fact table and the customer table is already filtered on the “To” side using RLS. Therefore, this relationship does not need to be set to “BothDirections.” However, if we set the relationship between the facts table and the contacts table to “BothDirections,” we see that the slicer for the customer field is also filtered. As a result, the user now only sees contacts associated with Dutch customers.
None
Setting the security setting to “None” means that filtering based on a relationship no longer occurs. Because row-level security is enabled on the customer table, users can view the full revenue in the fact table, but when viewed at the customer level, only the Netherlands is displayed. This ensures that the full figures can be viewed at the top-level, but not at the detailed level.
Security Testing
The Power BI service makes it easy to test security. You can read more about this in a previous blog that I wrote. The security testing process has since been modified slightly. Now, when testing a role as a specific user, you can also switch between reports. However, the reports must be in the same workspace.
Conclusion
By adjusting the various security settings, you can customize the filtering exactly to your liking. The default filtering is often sufficient for most needs, but this behavior can be adjusted if necessary. This can be particularly useful for hiding certain detailed information.
