If you are trying to solve the problem of creating different views for different users when they are using your report, this is the blog for you. Row Level Security (RLS) in Power BI allows you to set different restrictions on data access based on specific user types. This means that when users log into the report using their username and password, the report will adjust according to their role in your company, eliminating the need to create different reports for different user types.
Step 1: Define rows and rules in Power BI Desktop
The foundation of RLS is creating a table that contains information about which users have access to which ranges of data. You can either create this table in Power BI or obtain it from a data source where it exists.
The table should indicate different users in one column and the information available to them in a second column. For example, User1 can see the data only if the value in the Country/Region column is either “United States” or “Canada.” You can replace the values in the User column with actual usernames or email addresses from your organization.
Step 2: Create a fact table
To ensure that the predefined user restrictions flow to the dataset used to build the dashboard, create a fact table that contains all the unique values in the Country/Region column.
Step 3: Set up connections between the tables
Since RLS is primarily based on the User table, it controls country accessibility for each user, and all security filters from this table will be applied to the Orders table. To achieve this, ensure that the cross-filter direction is set to ‘Both’ between the User table and the Region table, allowing the security filter to restrict access in both directions. The cross-filter direction between the Region table and the Orders table should be set to ‘Single’ to enable filtering from the Region table.
Step 4: Create a security role
In the ribbon, click on ‘Manage Roles’ to add a new role. Select the User table, rename it to ‘User Security’, and switch to the DAX editor. Then, use the USERPRINCIPAL() function to filter data.
Step 5: View as a User
In the modelling section of the ribbon, select ‘View as’ and check ‘Other user’ and ‘User Security.’ Choose the user you want to view as, in this case, we are viewing as User 4.
Since User 4 should only be allowed to see data related to the United States, the following is what User 4 will see on the report.