When you want to restrict the rows visible to a single user, you must apply a security rule to the dataset, so that regardless of the report displayed or edited, the user cannot access data that he is not
168 CHAPTER 8 | Using Microsoft Power BI in your company
allowed to see. For example, the managers of China or Europe should see only data relevant to their area, even if all of them use the same report. This type of security is known as row-level security.
If you are using a live connection to Analysis Services or you created a model by using DirectQuery, you must implement row-level security on the source database, and you cannot modify its behavior in Power BI.
If you have a model that imported data in the Power BI service, you can apply row-level security to the dataset. You can manage row-level security by selecting the Security action available for datasets, as you can see in Figure 8-20.
Figure 8-20: Selecting Security to activate row-level security on a dataset.
In the row-level security configuration, you create one or more security roles, which define the rows a user can see in each table. You can find a step-by-step guide to configuring row-level security at https://powerbi.microsoft.com/documentation/powerbi-admin-rls/.
Note As of this writing, this feature is in preview, and details might change very quickly. You might want to refer to the online documentation to see if there have been any updates to the user interface.
Figure 8-21 shows the final result of a security role (named China) providing access to only those rows corresponding to sales made in China.
Figure 8-21: The configuration for row-level security for limiting access to sales made only in China.
Each role contains one or more members; these are the users who can access the model through the role. The rules defined for the role are in the form of a DAX expression for each table. A row in a table is visible if the condition, for that row, is true. If a user belongs to more than one role, he will have access to all the rows that are visible in at least one of his roles. However, if a user does not belong to any security role and the dataset has row-level security active, he will not see any data for that
169 CHAPTER 8 | Using Microsoft Power BI in your company
dataset, regardless of whether he can access the dashboard containing data from that dataset because it has been shared by another user.
When you restrict access to a table that has a one-to-many relationship with other tables, you restrict access to the related tables, too. Consider a model with two tables: Customers and Sales. Applying a security rule to Customers also restricts Sales, showing only the sales related to a visible customer.
Let’s take a look at what happens when David creates the row-level security rule shown in Figure 8-21, assigning Wendy Kahn as a member of the role, and then he shares with her the dashboard depicted in Figure 8-22.
Figure 8-22: A dashboard shared by David, showing China, Germany, and the United States.
When Wendy opens the same dashboard shared by David, she sees only China; Germany and the United States are not visible, as illustrated in Figure 8-23.
Figure 8-23: A dashboard displayed to Wendy, showing only China.
The security restrictions applied to the dashboard Wendy received from David are also applied to any other visualization shown to Wendy and are based on the same dataset. By applying security at the row level, you can easily customize the aggregations visible to each user. However, keep in mind that
170 CHAPTER 8 | Using Microsoft Power BI in your company
you cannot prevent a user from viewing a particular table, column, or measure. The row-level security filters rows, not columns or other entities of a certain dataset. When you share a dashboard and its reports, in theory the user consuming the data cannot gain access to other entities (measures, columns) that were not published in the report, but this is not guaranteed by the row-level security filter and cannot be enforced at the dataset level. For this reason, if you need to ensure that certain measures are not visible to a group of users, you should consider creating a separate model for that, including only columns and measures that can be made visible to all of the users who can access a certain dataset.
Note As of this writing, the row-level security feature is in preview. It has a number of restrictions that might be lifted or removed in subsequent releases. As of now, you can apply row-level security only in datasets included in My Workspace, but not in group workspaces and not in datasets published in content packs. You can add only single users as role members, not user groups or distribution lists. You can apply it only to datasets created by using Power BI Desktop, not to datasets created with Power Pivot for Excel (but you can import such a model in Power BI Desktop and then publish the Power BI Desktop file). However, when you publish a new version of the Power BI Desktop file, all the existing security roles are removed completely. DirectQuery is not supported for row-level security. Q&A and Cortana are not supported by row-level security, so Q&A input is not visible if role-level security is active for all the models related to a shared dashboard.