While Using RLS When Manipulating Relationships in Power BI, What Can Go Wrong?

When we have RLS in place, there are some restrictions when we try to manipulate Relationships. However, Microsoft’s documentation doesn’t provide many details on this topic. So, let’s dissect this. You will experience a big surprise.

Introduction

In Dax, when we want to manipulate Relationships between tables, we can use one of these functions:

We can find the following sentences in the Microsoft documentation about the interaction between these two functions and RLS:

Figure 1 - Part of MS Documentation about CROSSFILTER() and USERELATIONSHIP() and RLS (Figure by the Author) — Figure 1 – Part of MS Documentation about CROSSFILTER() and USERELATIONSHIP() and RLS (Figure by the Author)

While the sentence is unclear for CROSSFILTER(), it is much clearer for USERELATIONSHIP():

These functions don’t work correctly when manipulating relationships between tables affected by RLS rules.

But what does this mean?

Let’s look at it in more detail.

The Data model and the Report

For this example, I work with the following data model:

Figure 2 - Data model used for this example (Figure by the Author) — Figure 2 – Data model used for this example (Figure by the Author)

Three things are important:

The table ‘Date’ has three Relationships to ‘Online Sales’: Order, Due, and Ship Date.
The ‘Geography’ table has Relationships to the ‘Store’ and ‘Customer’ tables, but only the Relationship to the ‘Store’ table is active.
The table ‘AccessByCountry’ contains some users with an assigned country. It controls each user’s permissions.

The Report contains the following Visuals:

Figure 3 - Report used for the Example (Figure by the Author) — Figure 3 – Report used for the Example (Figure by the Author)

I added an RLS Rule to the table ‘AccessByCountry’:

Figure 4 - Add an RLS role to the table 'AccessByCountry' to check for the current User (Figure by the Author) — Figure 4 – Add an RLS role to the table ‘AccessByCountry’ to check for the current User (Figure by the Author)

Manipulate the relationships

I use the USERELATIONSHIP() function for two new Measures, ‘Sum Online Sales by Ship Date’ and ‘Sum Online Sales (By Customer Location)’:

Sum Online Sales by Ship Date =
                   CALCULATE(Sum Online Sales],
                             USERELATIONSHIP('Online Sales'[ShipDate]
                                            ,'Date'[Date])
                             )

And:

Sum Online Sales (By Customer Location) = 
    CALCULATE([Sum Online Sales]
                ,USERELATIONSHIP('Customer'[GeographyKey]
                                ,'Geography'[GeographyKey])
                )

This way, I activate the Disabled Relationships while calculating the Measure.

Later on, I will create a Measure using the CROSSFILTER() function.

Now, it’s essential to understand which Measure uses which Relationship and if it’s affected by the RLS role:

The Measure ‘Sales by Ship Date’ manipulates the Relationship to the Date table. No RLS role is set up on either of these tables.
The Measure ‘Sum Online Sales (By Customer Location)’ manipulates the relationship between ‘Customer’ and ‘Geography’.
The RLS role is set up on the table ‘AccessByCountry’, which filters the ‘Geography’ table.
The RLS indirectly filters the ‘Online Sales’ table through the ‘Store’ and ‘Customer’ tables.

Test Measures with RLS using USERELATIONSHIP()

Now, I test the RLS role by using the ‘View as’ feature:

Figure 5 - Test the RLS role using the Email-address for david2@contoso.com, which has access to Germany (Figure by the Author) — Figure 5 – Test the RLS role using the Email-address for david2@contoso.com, which has access to Germany (Figure by the Author)

As soon as I click on OK, the RLS role is activated, and I see what happens:

Figure 6 - Error for the Visual using the Measure which manipulates the relationship with the Geography table, but no error for the one using the Date table (Figure by the Author) — Figure 6 – Error for the Visual using the Measure which manipulates the relationship with the Geography table, but no error for the one using the Date table (Figure by the Author)

As you can see, the Measure using the Ship Date still works, as no RLS role affects the Date table.

However, the measure that manipulates the relationship to the ‘Geography’ table no longer works. Please note that both functions (USERELATIONSHIP() and CROSSFILTER()) are mentioned in the error message.

The reason is that the USERELATIONSHIP() function has the potential to circumvent the RLS role, rendering it ineffective.

This is why it is not allowed.

Interestingly, the Measure, which manipulates the Relationships to the Date table, still works, even though the Measure still affects how the data in the ‘Online Sales’ table is filtered. Power Bi recognizes that no RLS role affects the ‘Date’ table.

Test Measures with RLS using CROSSFILTER()

Now, let’s try something different:

I want to calculate the ‘Sales by Customer Location’ percentage related to Sales in all Regions.

For this, I create the following Measure:

% Sales vs all Customer Type = 
    [Sum Online Sales]
    /
        CALCULATE([Sum Online Sales]
                ,REMOVEFILTERS('Geography')
                )

This is the Result:

Figure 7 - Result of the new Measure to get the Ratio of Sales vs all Stores from all Regions and with the Customer Type column from the Customer table (Figure by the Author) — Figure 7 – Result of the new Measure to get the Ratio of Sales vs all Stores from all Regions and with the Customer Type column from the Customer table (Figure by the Author)

This measure works without problems, even when tested with the RLS role.

As there is only one Store in each Country and City, the result, when using the RLS Role, always returns 100%.

The Syntax above can be changed by using the CROSSFILTER() function. By setting the Relationship between the ‘Store’ and ‘Geography’ tables to None:

% Sales vs all Geographies = 
    [Sum Online Sales]
    /
        CALCULATE([Sum Online Sales]
                ,CROSSFILTER('Geography'[GeographyKey]
                            ,'Store'[GeographyKey]
                            ,None)
                )

The result is precisely the same as before.

But when testing the RLS role, we get a surprise:

Figure 8 - Result of the new Measure when testing the RLS role. (Figure by the Author) — Figure 8 – Result of the new Measure when testing the RLS role. (Figure by the Author)

This result is weird!

To understand what happens, let’s add a Measure without the Division, only with the second part of the Measure above:

Figure 9 - Result of the Measure calculating all Sales for all Regions using CROSSFILTER() and while testing the RLS role. (Figure by the Author) — Figure 9 – Result of the Measure calculating all Sales for all Regions using CROSSFILTER() and while testing the RLS role. (Figure by the Author)

When we add some more Measures to compare the results, we can see what happens:

Figure 10 - Analyzing the Results by adding more Measures and without activating the RLS testing (Figure by the Author) — Figure 10 – Analyzing the Results by adding more Measures and without activating the RLS testing (Figure by the Author)

As you can see, the Sales for Germany calculated by the Customer’s Geography (See above for the Measure) are precisely the same as the result of the Measure, which uses CROSSFILTER() to deactivate the Relationship between Store and Geography.

This means the following:

I use CROSSFILTER() to Disable the Relationship between the tables ‘Store’ and ‘Geography’.
I test the RLS role. The RLS expression filters the ‘Geography’ table.
Power BI detects the (inactive) relationship between ‘Geography’ and ‘Customer’ and activates it to calculate the result.

This leads to unexpected, misleading, and wrong results, which must be avoided.

While using USERELATIONSHIP() causes an error, using CROSSFILTER() can change the result unexpectedly.

The above example is not very practical, as using REMOVEFILTER() is more straightforward. I only wanted to give you an example to show you what happens.

Solving the problem

This scenario is not uncommon, although I do not recommend building a data model in this way.

In such a specific scenario, I recommend integrating the columns from the Geography table into the Store and Customer tables.

However, adding Relationships from the ‘AccessByCountry’ table to both tables, ‘Store’ and ‘Customer’, is impossible. This will create ambiguity, which Power BI will not allow.

Therefore, I must duplicate this table and connect each of them to the two tables, ‘Store’ and ‘Customer’ individually:

Figure 11 - Modified data model (Figure by the Author) — Figure 11 – Modified data model (Figure by the Author)

Now, I can set up the RLS role to filter both tables. I can even set up different rules to allow separate access to the Stores and Customer’s Geographies.

I can set up my Measures as needed and manipulate the filter without restrictions.

I no longer need to separate Measures, for example, by Store Geography or Customer Geography, as I must only use the columns from the correct table.

OK, now I have the same content twice (Each Geography column for both tables, ‘Store’ and ‘Customer’). However, they are each in their separate table, so this shouldn’t be a problem.

Figure 12 - Result after remodeling and while testing the RLS role (Figure by the Author) — Figure 12 – Result after remodeling and while testing the RLS role (Figure by the Author)

Conclusion

You must pay extra attention when using RLS roles.

While using USERELATIONSHIP() and CROSSFILTER() is common in several scenarios; this can cause issues when RLS roles are set up in the data model.

As you have seen above, using them is no problem when the relationship is unaffected by any RLS role.

But as soon as you try to manipulate a Relationship affected by an RLS role, you can have issues, especially when using CROSSFILTER().

Weirdly, you get potentially unexpected results instead of an error message. These results can be challenging to explain.

In my example, the results are OK when the (inactive) relationship between Geography and Customer is removed. The behavior shown above is specific to my data model.

But, as you have seen, all problems disappear with some tweaks in the data model.

As I have stated multiple times in previous pieces, a good data model is the base for a good solution in Power BI.

References

Like in my previous articles, I use the Contoso sample dataset. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be freely used under the MIT License, as described here.

I changed the dataset to shift the data to contemporary dates.

Get an email whenever Salvatore Cagliari publishes.

I make my articles accessible to everyone, even though Medium has a paywall. This allows me to earn a little for each reader, but I turn it off so you can read my pieces without cost.

I write these pieces in the evenings and at the weekends, which is a lot of work.

You can support my work through

https://buymeacoffee.com/salvatorecagliari

Or scan this QR Code: