Once we grant access to a Power BI report to various users in the organization, we might consider that users shouldn't have access to all the information, but only to part of it—their own part. That is, the information should be filtered to show and calculate only based on the records belonging to the user. This is a common scenario in most companies where data access security plays an important role.
Similarly, another scenario could be that, for example, in a Power BI sales report, we want each branch to access only its own data. We can divide the organization into sales areas and create a role for each area; from there, it would simply be a matter of assigning the user to the corresponding role.
This is when we talk about Row-Level Security, which is established by creating roles in Power BI Desktop. In the role, a DAX-formulated condition is set, such as Area Code = "Area" or, similarly, if any of the data model's tables contains a field with the email or user used to validate the user, we can associate it with the expression userprincipalname().
We see it in the following steps in an example where our analysis will be performed on a Business Central Absence Registration table, and we want each user to see only their absences and only those of the Vacation type:
- From the Modeling tab, we go to Manage Roles.
- In the window that opens, we assign a name, and on the corresponding table, we write our DAX expression or use the new Editor, which doesn't require knowing DAX (currently in preview).
There are some limitations with the Editor; for example, certain expressions couldn't be used, and you'd have to do it with the DAX Editor.
- We include a new filter in the previous one through the userprincipalname() expression.
- The next step would be to Publish to the Power BI service.
- Once published, we go to the Dataset, to Security, and there we add users to the corresponding role.
With these two filters, each user would only see their absences and only those of the VACATION type.
If you want expand information about Power BI and how to connect it with your ERP and other data sources, don't hesitate to contact ABD Consulting and IT Solutions.