Implementing Dynamic User-Based Visibility in Power BI

Data security, wherein users or groups of users are prevented from viewing a portion of a dataset, is often a top requirement in Power BI deployments. Security implementations can range in complexity from mapping a few security groups to their associated row-level security roles based on a single dimension value such as department or region to dynamic, user-based security involving dedicated user permissions tables and dynamic DAX functions. Given the variety of use cases and the importance of this feature to securely share a dataset across stakeholders, it is important to understand the process and techniques available for developing, testing, and operationalizing data security roles.

In addition to row-level security (RLS) roles, dynamic, user-based filter context techniques can also be used to simplify and personalize the user experience. For example, the filter conditions built into reports, as well as the interactive...

Technical Requirements

The following are required to complete the recipes in this chapter:

• Power BI Desktop.
• SQL Server with the AdventureWorksDW2019 database installed. This database and the instructions for installing it are available here: http://bit.ly/2OVQfG7.
• Files for this chapter and can be downloaded from the following GitHub repository: https://github.com/PacktPublishing/Microsoft-Power-BI-Cookbook-Second-Edition.

Capturing Current User Context

The foundation of dynamic user security and visibility in Power BI is the ability to extract the user principal name (UPN) or login credential of the business user connected to content in the Power BI service. The USERPRINCIPALNAME DAX function retrieves this text value and thus enables filter expressions to be applied to the tables of a model in security roles. In addition to RLS roles, which override and impact all DAX measures of a dataset, the UPN or "current user" text value can be used by DAX measures to retrieve the UPN prefix and suffix, or filter other measures.

In this recipe, DAX measures are added to a data model to dynamically retrieve the UPN as well as the UPN's prefix and suffix. Additional detail on authentication in Power BI and the USERNAME function, an alternative dynamic DAX function that also retrieves the UPN in the Power BI service, is also covered.

Getting ready

To prepare for this recipe, follow these...

Defining RLS Roles and Filter Expressions

Data security should always be top of mind and BI teams and developers should strive to ensure that users are never granted greater access or permission than is necessary to perform a legitimate business function.

In the context of Power BI projects, the question "Who should be able to see what?" should be well defined and documented before any reports are published or read access to the dataset is granted. Additionally, there should be a well-defined process for creating and managing the security groups used in security implementations.

The data security of Power BI models comprises security roles defined within the model, with each role containing a unique set of one or more DAX filter expressions. Roles and their associated filter expressions are created in Power BI Desktop, and users or groups are mapped to security roles in the Power BI service. A single DAX filter expression can be applied to each table of a model within...

Designing Dynamic Security Models

Dynamic security models in Power BI filter tables are based on the relationship of the logged-in user to a column or columns stored in the data model. The USERPRINCIPALNAME DAX function returns the user's UPN, as per the first recipe of this chapter, and a filter expression of a security role accepts this value as a parameter. Like all filters in Power BI data models, the filters applied in security roles also filter other tables via one-to-many and bidirectional relationships. Security roles can also blend dynamic, user-based filters with standard security filters to further restrict the visibility of members mapped to these roles.

This recipe implements dynamic security on an Employees dimension table. The result is that logged-in users (employees) can only view their own data and the data of those who report to them directly or indirectly via other managers.

Getting ready

To prepare for this recipe, follow these steps:

...

Building Dynamic Security for DirectQuery

Dynamic row-level security roles can be implemented in DirectQuery models via relationships, and with specifically bidirectional cross-filtering between user security tables and the dimension tables to be secured. DAX information functions, commonly used in the role security expressions of Import mode models, such as CONTAINS and LOOKUPVALUE, are not supported in DirectQuery mode models, thus requiring a relationship-based security design. Although limited to this single approach, dynamic security can be quickly developed for DirectQuery models and maintained easily, without the need for complex DAX security expressions.

This recipe walks through the steps and settings necessary to support dynamic security in a DirectQuery model.

Getting ready

To prepare for this recipe, follow these steps:

1. Open a Power BI Desktop file locally and access the Power Query Editor by clicking on Transform Data in the ribbon of the Home tab...

Displaying the Current Filter Context

DAX measures can be created to dynamically display the current filter context to report users. These measures can detect filters, retrieve values, and apply conditional logic to the filters applied to both slicer visuals and report- and page-level filters. With the filter context as a visual aid, users consuming or interacting with Power BI reports can focus on the data visualizations to obtain insights more quickly and with greater confidence. In addition, such measures can be useful when designing RLS scenarios as a check that the correct information is being filtered by the RLS rules.

In this recipe, DAX measures are created to detect and display the filter selections applied to a specific column, either on the report canvas itself or as a report- or page-level filter. An additional example displays the values of a column that are "remaining," given the filters applied to the column directly and indirectly via other filters.

...

Avoiding Manual User Clicks

A common scenario in BI projects is the need to customize a core set of reports and dashboards to better align with the responsibilities and analytical needs of specific roles or users within a larger team or organizational function. A given business user should, ideally, have immediate and default visibility to relevant data without the need to interact with or modify content, such as applying filter selections.

Power BI's extensive self-service capabilities are sometimes a solution—or part of it—to this need, and additional role-specific, IT-supported reports and dashboards are another realistic option.

A third option, and the one that is the subject of this recipe, is to embed user-based dynamic filtering logic into DAX measures. With this approach, a single or small group of reports and dashboards can be leveraged across multiple levels of an organization, thus avoiding the need for new report development.

Getting ready...

Conclusion

This chapter contains detailed examples of building and deploying dynamic, user-based security for both import and DirectQuery datasets as well as examples of dynamic filter context functionality to enhance the user experience. Data security where users or groups of users are prevented from viewing a portion of a dataset is often a top requirement in Power BI deployments and security implementations can range from simple to complex. Given the variety of use cases and the importance of this feature to securely share a dataset across stakeholders, it is important to understand the process and techniques available for developing, testing, and operationalizing data security roles.