Inactive relationships in a Power BI model have probably been encountered by everyone who has set their first steps in Power BI. As there can only be one active path between two tables in a model, just linking everything that looks related (like most beginners in Power BI do) results in ‘dotted lines’ rather sooner than later. Obviously, proper data modeling needs to be done, moving from inactive relationships created ‘by accident’ to ones that are designed for specific purposes.

A typical use of inactive relationships is to link facts to different dates. This way, a sales transaction can be reported on e.g. order date to analyze revenues, or on payment date to analyze payments and cash flow:

An inactive relationship can be activated within the scope of a DAX calculation using the USERELATIONSHIP DAX function in combination with CALCULATE:

USERELATIONSHIP activates the inactive relationship, effectively inactivating other relationships.

All this is perfectly fine to do in a Power BI model. There are, however, some things to consider when adding security to a model. Using inactive relationships in combination with row-level security can lead to errors that can be somewhat unexpected, but that do make sense when you think about it.

Let’s consider the model diagram below.

The facts in this model record hours worked by employees of a company. As it goes, most of the hours worked are spent on projects, which could be projects that are paid for by customers or projects that are otherwise considered to be productive; these are called direct hours. But employees also spend time on non-project related work, like internal meetings; or are on sick leave. These are called indirect hours.

Projects have project managers, and we want to be able to retrieve information on total hours spent on projects but also aggregated by project manager. So, selecting Anna, for example, we could calculate the amount of time spent on the projects managed by Anna, or the indirect hours for Anna herself. The DAX calculations to do this, are rather straightforward with USERELATIONSHIP:

Now, let’s add security to this model. After all, it makes sense that Anna can see her own indirect hours and hours spent on her projects, but not necessarily indirect hours of people working on her projects. For testing, we can add a simple DAX security expression implementing static security for Anna alone, restricting access to the Employee table to only Anna’s row:

Row-level security can be set without issues in this model. However, only when activating the inactive relationship by using the Hours (Indirect) measure in a report, an error is raised:

What happened? The error message states that we have now two active paths between two tables. Apparently, while USERELATIONSHIP normally inactivates an existing, active relationship, setting row-level security causes the active relationship to not become inactive.

While this may sound odd at first, it makes perfect sense. Or, let’s make this very clear: this is exactly what you want! Being able to effectively disable a security filter by changing the cross-filter behavior of relationships would render security useless. Even more so when you realize that users of a Power BI model can add new measures in a report without having to edit the model itself.

Anyway, the conclusion here is to be very careful when using inactive relationships in combination with row-level security. But what is the right way to do this? There are two options I can think of.

The first is to implement RLS not on the Employee filter table, but on the fHours fact table. This requires a slightly more complex DAX security filter on fHours; additionally, it does not secure the Employee table, nor the Project table.

The second option, and probably the better one, is to split the fHours fact table in two: one for direct hours and one for indirect hours. This eliminates the need for an inactive relationship and allows for applying custom security logic to both tables.