Power BI DAX: RLS, USERPRINCIPALNAME

[Email Address] =
USERPRINCIPALNAME ()

The DAX function USERPRINCIPALNAME() returns the current user's login credentials in the format of an email address, such as user@domain.com. This function is particularly useful in Power BI for creating personalized reports and implementing row-level security, as it allows you to filter data based on the user who is currently accessing the report.

For example, if you have a measure [Email Address] = USERPRINCIPALNAME(), it will dynamically return the email address of the user viewing the report. This can be used to tailor the data displayed to each user, ensuring they only see information relevant to them.

Implementation Steps

To implement dynamic row-level security (RLS) in your Power BI data model so that users see only their own employee data, you can use the USERPRINCIPALNAME function. Here's how you can set it up:

1. Create a Role in Power BI Desktop:

• Go to the "Modeling" tab.

• Select "Manage Roles".

• Create a new role, for example, "EmployeeRole".

2. Define the DAX Filter:

• In the "Table" dropdown, select the Employees table.

• In the "Table filter DAX expression" box, enter the following DAX expression:

[Email Address] =
USERPRINCIPALNAME ()

This DAX expression ensures that only the rows where the Email Address matches the current user's principal name (email address) are visible.

1. Test the Role:

• After creating the role, you can test it by selecting "View as Roles" and choosing the "EmployeeRole" to see the data as it would appear to a user with that role.

2. Publish to Power BI Service:

• Once you have tested the role in Power BI Desktop, publish your report to the Power BI service.

• In the Power BI service, go to the dataset settings and assign users to the "EmployeeRole".

Example Scenario

Consider the following Employees table:

When a user logs in, the USERPRINCIPALNAME() function will return their email address.

For example, if Simon logs in, the DAX filter [Email Address] = USERPRINCIPALNAME() will ensure that only Simon's data is visible:

This setup ensures that each user sees only their own data, meeting the requirements for dynamic row-level security in both Power BI Desktop and the Power BI service.