When it comes to making use of the row-level security features available within Power BI while having multiple roles with the same or different levels of access in the hierarchy, expect that you will have to build a model in which data is dynamically filtered out based on the corresponding role and its permissions in the context of an organization. Here is a detailed procedure to do so in a more systematic way:
1. Define Role-Based Security Requirements
Begin with decomposing the business objective in terms of actors operating within it and the degrees of data access that are credited for each of them. For illustrative purposes, one may consider a situation in which an acting manager would be able to see the whole department's data. At the same time, a regional director's access would span several departments. Precisely defining these roles would be instrumental in instruction on where and how to apply security filters and hierarchies in Power BI.
2. Create Security Tables
Implement a security table within the data model that outlines the User, role, and hierarchy levels. This table will consist of user names, roles, and even hierarchy information, regions, departments, or teams. It will instruct Power BI to apply filters in accordance with a user's level of access and role.
For example, if a hierarchy is present with levels like Region > Department > Team, the security table should have fields for these levels. Also, ensure that this table is related to the relevant data tables so effective access control is in place.
3. Set Up RLS Roles in Power BI
In Power BI Desktop, navigate Modeling > Manage Roles. In this section, create roles according to the requirements that you have specified that the users should meet, e.g., 'Regional Manager,' 'Department Head,' etc. For each role, add a DAX filter statement to extend the relevant security table, which restricts the available information based on the role and level.
For example, a DAX expression [User] = USERPRINCIPALNAME() can be written to get the name of the person logged into the system and compare it accordingly.
4. Check RLS functionality with Power BI Desktop
Using Power BI, you can test the practical usage of the roles created to ensure that they work as intended. The View as Role feature allows you to try different users' perspectives and check that the data each role has access to is restricted to what is permitted. This part is crucial to supporting or diagnosing any implications in your RLS and ensuring that each and every hierarchy is followed.
5. Publish And Administer In Power BI Service
After configuring RLS in Power BI Desktop and performing successful tests, publish the report to the Power BI Service. In the Service, you can assign users to roles under the Security settings of the dataset. It is very important to manage roles because users change roles, and access requirements change within the organization.
With the help of a well-thought-out RLS design and some simple DAX code for dynamic filtering, the hierarchical security in Power BI can be managed so that each User sees only the data that he or she is permitted to view.
