Power BI row-level security

Companies often have the need to restrict certain employees or groups from seeing part of their business data. Power BI solves this by using Roles and defining the rights at the leven of Workspace in which they work. In practice, this means that everyone sees exactly what they are allowed to see. Nothing more, nothing less! In the following text you will see how Power BI row-level security is being implemented …

We start our journey with Power BI. After logging in, we first need to create a new workspace. This is accomplished by going to the Workspaces menu and then selecting the Create a Workspace option. A menu will appear on the right to specify the name of the workspace, a brief description, and you can optionally add an image. Let’s call it „Čokolada“ (Chocolate). By clicking Save button, we save the data and a new workspace is created!

Since the author has been on a diet since (some) Monday to demonstrate these functionalities, we will use the “chocolate example”. The example consists of three tables: Artikli (Items), Komercijalisti (Salesmen) and Transakcije (Transactions). The first contains information on articles (chocolate products), the second contains information on salesmen, and the third contains the date, quantities of items sold, item codes and salesmen. After we run Power BI Desktop, we will import tables into a data model and connect them to create a dataset to use for creating reports.

Next, we need to create a simple report that is made up of several visuals. First we will select Table visual. We will add the IMAGE, ITEM NAME and QTY dimensions to it. The result is a table where we can see the photo and name of the item, as well as the quantities sold for that item. Then we add the Clustered Column chart. We will show the manufacturers on the x axis and the quantities sold on the y axis. Finally, we add another Slicer containing the salesman’s first and last name. We will rename the page to „Čokolada“, and we will beautify the report by adding a single image, aligning objects, and adding the desired formatting elements.

To understand how row-level security works, we need to create several role-based scenarios. What a lot of companies require is commercialists do not see the sale of their counterparts. This can only be seen by their superior. Therefore, we will first create three roles, each of which serves to filter the data to see sales only for the chosen salesman.

We create roles by entering the Modeling ribbon and selecting the Manage Roles option. When the window opens, we create a new role by clicking the Create button. First, we’ll create a role called “Jelena”, and it filters the data so that the [IME I PREZIME] (NAME&SURNAME) column in the Komercijalisti (Salesmen) table has the value “Jelena Aleksic”, which is achieved by writing a simple DAX statement. Similarly, we will make the roles of “Marko” and “Petar”.

You can test the role if you like. To do this, run the View As option and select the role you want to view the data from. If we select the role “Jelena” you will notice that Power BI Desktop informs us that we are watching data with this role. At the same time, the data is filtered and we only see Jelena’s sales. The easiest thing to notice is that there are no other salesmen on the slider.

After we have made the reports we need to take the document and publish it in the cloud. When posting, note that this happens within the „Čokolada“ workspace.

Now back to Power BI service. If you select the desired workspace in its upper right corner there is a shortcut to enter the two menus. The first of these Workspace Settings serves to change the general settings related to the workspace, and the second Workspace Access is used to define roles. We can add users here by entering their email address that is linked to an account on the Office365 portal. On this occasion, we assign them roles: Admin, Member, Contributor, Viewer. If we want to restrict the view of the data to someone, it must be defined as a Viewer. It is important to note that row-lewel security only works with Power BI Professional subscribers. Those with free Power BI accounts cannot use these features.

How are roles activated? You need to go to the „Čokolada“ workspace, select the dataset, and then enter the menu. Next, the Security option should be selected. On the right will open a panel where we can see the three roles we created recently. By specifying the user’s email address and then clicking Add, we add it to the list of those who see the data filtered by the role. For example, if we add a user with the e-mail address jelena@excelk.com and assign him the role of “Jelena”, after logging into Power BI, this user will only see the sales data of the salesman “Jelena Aleksic”. Multiple roles can be added, but the most important thing to remember is that the user within the workspace must be defined as a Viewer.