Security with DAX

When working with data, chances are you deal with confidential data that must be secured. Even within an organization, some people should be able to see more than others. In a Power BI model, there are sophisticated ways to apply security. From this chapter, you will learn how to do this.

Note that we will not cover security in the distribution or sharing of reports and dashboards in Power BI. Instead, we focus on security within Power BI models. The typical scenario here is that two users of the same report see different report content, depending on their security settings.

This chapter covers the topics below:

• Securing a Power BI model with row-level security
• Configuring security on hierarchical data
• Securing attributes, or individual columns in a table
• Securing aggregation levels for calculated measures

  We will be using several models in this chapter that you can find in the GitHub repo for this book. Download the...

Introduction to row-level security (RLS)

With row-level security (RLS), you can restrict users from seeing all data that is in a Power BI model. RLS is the main form of security in a Power BI model. It is called row-level because you define which rows in each table in the model are visible to the user. Note that because RLS is set on the model level, any report that visualizes results from that model will satisfy the security policy defined on it.

Before diving in, let's make something clear: when you need a secure solution, you have to use RLS (or a related concept called object-level security, which we will discuss later in this chapter). There's just no way around it. Do not try to implement security through sharing reports (or not sharing, rather). You cannot oversee what will happen to the use of your model in the future: people may get self-service access to the Power BI model, get added to security groups by accident, or other things may happen. For the same reason...

Securing hierarchies using PATH functions

In most organizations, data is not directly related to a single user who has access to it. Instead, there is a group of people that each have access to different sets of data. Managers have access to the data of employees reporting to them, for instance. DAX contains a set of functions to deal with parent-child hierarchies like these: the PATH functions.

Hierarchical tables

First, let's take a look at a typical organization structure, in this case, that of QuantoBikes, our example company. QuantoBikes is organized into several divisions aligned with continents, and each division consists of multiple teams.

The organization map is pictured below:

Figure 2.1.17: QuantoBikes organization map

In the Employee table, this organizational hierarchy is registered by having a column called MngrNr, or Manager Number. This column contains the employee number for each employee's direct manager; only the CEO does...

Securing attributes

In this section, we will take a wholly different look at security in Power BI models. In the previous sections, we focused on ways to restrict the visibility of rows in tables of the model. This is the most common security need but, sometimes, other forms of security are needed. If you think of row-level security as "horizontal" security, looking at your data, then it makes sense to consider the possibility of "vertical" security. In other words, can we secure columns, or attributes?

The case for secured attributes

Securing a Power BI model with RLS is only really needed when the model is used by a broader audience. If you have a model used only by C-level executives, you probably won't need to have RLS at all, as each user is allowed to see all the data. Only when the audience grows larger does the need arise to segment the data based on geography, customer segment, or organizational structure, as discussed in the previous sections...

Securing aggregation levels

Yet another element in securing a Power BI model is related to aggregation levels. There could be a policy like "salary costs may be viewed by team, but individual employees' salaries can only be viewed by their direct manager." In this section, we explore options to secure viewing results on different aggregation levels.

Measures cannot be secured, fact tables can

We've mentioned it already earlier in this chapter: implementing security through DAX in measures is not secure. You should always design your model with a possible self-service user in mind, who will be able to write her own measures against the model. Through these, any security feature of your hard-wrought measures can be circumvented.

Instead, security must rely on the model structure and RLS only. This means that not every security policy that you can think of can be implemented. For instance, your users could ask for sales by individuals, but sales margin...

Summary

In this chapter, you have seen many aspects of securing Power BI models. Row-level security is a versatile feature, mainly because you can use DAX to implement sophisticated security filters.

Careful design is needed when implementing security in Power BI models, mainly due to the possibility of having multiple security roles, and because users may be a member of multiple roles. Not all security roles can effectively be combined in the same model, and security therefore even impacts decisions to split models.

With DAX, you can retrieve a user's identity and use that to determine what data is visible, allowing for highly personalized security settings. You can even navigate an organization's hierarchical structure using DAX PATH functions.

You have also learned that through effective combinations of modeling, DAX, and row-level security, you can achieve other forms of security, like value-level security to secure attributes, and securing aggregation levels...