Implementing Dynamic RLS in Power BI

Dynamic RLS ensures that only the right people get to see certain data and to keep sensitive information safe and private is very important. It adjusts data based on specific conditions ensuring that the people who meets those specific conditions get access to the data.

What was the reason for implementing dynamic RLS?

• I had list of users assigned different roles. Let us say there are total 8 users, 2 users are assigned with super admin role, 3 are assigned Admin, 1 is assigned Access Users and remaining users are assigned viewer role.
• I wanted to filter data based on roles.
• Now let’s see how the roles work, Super Admin have access to all data. Admins have access to all records with status “Access”, Access Users have access to their records along with records that have status “Access” and viewer can view only their own data.

How to implement dynamic RLS?

Let’s Open Power BI desktop.

• Go to Modeling Tab and Click on Manage roles.

• Click on New and Rename the Role Accordingly.

• Click on the table on which filter is to be applied.

• Click On Switch to DAX editor.

• After switching to DAX editor paste the code

Code

Security[Email]
    == USERPRINCIPALNAME ()
        || MAXX (
            FILTER (
                Security,
                Security[Role] = "Super Admin"
                    && Security[Email] == USERPRINCIPALNAME ()
            ),
            Security[Role]
        )
            == "Super Admin"
                || MAXX (
                    FILTER (
                        Security,
                        Security[Role] = "Admin"
                            && Security[Email] == USERPRINCIPALNAME ()
                    ),
                    Security[Role]
                ) == "Admin"        

Now let’s discuss how the code is working

• I have two tables on which I have applied the expression i.e. Security and Form.
• On Security Table, I used UserPrincipalName() along with MAXX Function to filter the role and to grant access to the user.
• UserPrincipalName() checks the current logged in user.
• The expression checks if the email in the 'Security' table matches the user's principal name.
• Then, it uses MAXX combined with FILTER to find if the role "Super Admin" is assigned to the current user's email. It checks within the 'Security' table for any roles that match "Super Admin" for that email.
• It performs a similar check for the "Admin" role.
• Here is first half done where roles are filtered. Now I want the admins to view data that have status Access and Super Admin to view all data.
• To do this I applied filter on second table named Form as the status column was in Form table.
• On Form Table DAX Editor Paste the code.

Code

IF (
    MAXX (
        FILTER (
            'Security',
            'Security'[Role] = "Super Admin"
                && 'Security'[Email] == USERPRINCIPALNAME ()
        ),
        'Security'[Role]
    ) == "Super Admin",
    TRUE (),
    IF (
        MAXX (
            FILTER (
                'Security',
                'Security'[Role] = "Access Users"
                    && 'Security'[Email] == USERPRINCIPALNAME ()
            ),
            'Security'[Role]
        ) == "Access Users",
        'Form'[Status] == "Access",
        IF (
            MAXX (
                FILTER (
                    'Security',
                    'Security'[Role] = "Admin"
                        && 'Security'[Email] == USERPRINCIPALNAME ()
                ),
                'Security'[Role]
            ) == "Admin",
            'Form'[Role] = "Admin"
                && 'Form'[Email] = USERPRINCIPALNAME ()
                || 'Form'[Status] = "Access",
            TRUE ()
        )
    )
)        

How does the code works?

• The If condition filters the data based on value.
• The Expression evaluates the role if it is super admin, it grants full access, if not "Super Admin" but is "Admin " with an "Access" status, grants access and if role is Access User and email matches logged in user then show all data of that user along with those record with an “Access” status.
• After formatting the code, Click on Save Button.
• After saving go to modelling tab and click on view as

• Click on the checkbox of role that was created, other user and type email of user

After applying these steps Dynamic RLS was applied to the report where users get to view report according to their assigned roles.

Conclusion

Using Dynamic RLS in Power BI provides security to data as it filters data according to the applied DAX expression. Dynamic RLS has made it easier to apply security according to the need just like filtering users based on their role. By following these steps, we can make sure that people get to see the data according to their role.

Special thanks to Maham Latif for her contribution to this article.