Develop and take a look at RLS Guidelines in Energy BI | by Salvatore Cagliari | Jun, 2023

A lot of my purchasers wish to limit entry to the info of their stories based mostly on particular guidelines.

Entry to knowledge known as Row Stage Safety (RLS briefly).

You’ll find many articles about RLS in Energy BI on Medium.

I added two of them within the References part under.

Whereas all of the articles do job of explaining the fundamentals, I all the time miss a proof on how one can develop extra complicated guidelines and how one can take a look at them simply.

On this article, I’ll clarify the fundamentals of RLS and add complexity step-by-step.

As well as, I’ll present you how one can use DAX Studio to construct queries to check RLS guidelines earlier than including them to the info mannequin.

So, right here we’re.

I exploit the State of affairs the place Customers get entry to Retail Gross sales knowledge based mostly on Shops or the geographic areas of Shops throughout the firm, together with a mix of each.

Within the Contoso knowledge mannequin, I exploit the next tables:

I create the next report to check my outcomes:

To create an RLS Rule, you could open the Safety function editor:

Subsequent, you possibly can create a brand new Function and set the title for this Function:

In my case, I put the title to “StorePermissions”.

Now, I can begin including an expression to regulate entry to the Retailer desk:

We now have had a brand new, easier editor for RLS guidelines for a number of months.

In my case, I wish to add a DAX expression. So, I click on on the “Change to DAX editor” button.

At first, I add the best doable expression: TRUE()

To grasp RLS Guidelines, you need to know that entry is managed by the output of the expression within the RLS rule editor.

The person will get entry if the output of the expression is just not empty or FALSE().

In precept, any expression within the RLS rule editor is added as a filter to any question.
Let’s have a look at the impact of this primary expression earlier than I clarify this in additional element.

To check the Rule, I save the expression and shut the Editor.

Now I can View the report with the brand new Rule:

On high of the report web page, you will note a yellow banner displaying that you’re wanting on the report utilizing the StorePermission Rule.

Because the StorePermission rule doesn’t limit entry, you’ll not see any distinction.

Let’s strive one thing totally different.

Now I modify the Expression within the RLS rule to FALSE().

Once I take a look at the Rule, I can’t see any knowledge:

This proves that knowledge is accessible if the expression doesn’t return FALSE().

To grasp this impact intimately, let me present a DAX question to get the consequence with none restrictions:

EVALUATE
SUMMARIZECOLUMNS(
Retailer[Store]
,"Retail_Sales", 'All Measures'[Retail Sales]
)
ORDER BY Retailer[Store]

Once I add an RLS rule with TRUE(), as proven above, the question adjustments to a question just like this:

EVALUATE
FILTER(
SUMMARIZECOLUMNS(
Retailer[Store]
,"Retail_Sales", 'All Measures'[Retail Sales]
)
,TRUE()
)
ORDER BY Retailer[Store]

I enclosed the question inside a FILTER() perform and added TRUE() because the filter expression.

Within the following examples, I’ll use CALCULATETABLE(), as writing the code is extra environment friendly and versatile.

Extra on this in a bit.

Subsequent, I wish to limit entry to all Shops containing the “Contoso T” String.

For this, I modify the Expression within the Rule editor to the next:

CONTAINSSTRING('Retailer'[Store], "Contoso T")

When testing the rule, I get the next consequence:

It will be good to check the end result of such a rule with a DAX Question.

On this case, I exploit the next Question in DAX Studio to examine the Consequence:

EVALUATE
CALCULATETABLE(
SUMMARIZECOLUMNS(
Retailer[Store]
,"Retail_Sales", 'All Measures'[Retail Sales]
)
CONTAINSSTRING('Retailer'[Store], "Contoso T") = TRUE()
)
ORDER BY Retailer[Store]

The interior half, with SUMMARIZECOLUMNS(), generates the output desk.

On this case, I’m solely within the checklist of the shops.

Then, I enclose the SUMMARIZECOLUMNS() name with CALCULATETABLE() so as to add a filter to the question.

On this case, I add the expression from the RLS rule, together with an “= TRUE()” examine.

The result’s the next:

However what occurs below the hood?

Let’s have a look at the Storage Engine Question:

And what occurs once I apply the RLS rule to this question?

I can apply an RLS rule from DAX Studio with a number of clicks:

The Storage Engine question is the next:

The primary question (Line 2) retrieves the checklist of all Shops.

The second question contains the RLS rule within the WHERE clause.

As a substitute of getting the checklist matching Shops (In accordance with the Filter), we see a cryptic line, which incorporates the RLS rule.

You’ll be able to see that the results of the Storage Engine (SE) question nonetheless incorporates 309 Rows, like above, which is the variety of all Shops + 3 rows.
A touch why now we have the discrepancy of three rows is within the textual content under the SE question: Estimated dimension: rows = 309

The precise variety of rows returned could also be certainly 306.

However this evaluation exhibits that RLS guidelines are utilized after the Storage Engine, because the question consequence incorporates solely 21 rows: All Shops which begin with “Contoso T”.

That is essential, because the Components Engine (FE), which can compute the ultimate consequence after the Storage Engine, is single-threaded and may use just one CPU Core.

Whereas the SE is multi-threaded and may use a number of CPU cores.

Consequently, we should chorus from writing inefficient code into the RLS rule.

Subsequent, I wish to mix two expressions:

1. Solely Shops beginning with “Contoso T”
2. Solely Shops in Europe

To attain this, I add a second expression to the Geography desk utilizing the easy editor:

Once I swap to the DAX Editor, I get the next expression:

Discover the usage of the strict equal operator.

Altering to the simple equal operator on your expression could be needed.

That is the consequence when testing the rule:

The DAX question for this rule will appear like this:

Now, let’s add one other stage of complexity to the RLS rule:

I wish to limit entry to the Shops which both:

• The title of the Shops begins with “Contoso T” and are in Europe
  or
• The title of the Shops begins with “Contoso S” and are in North America

This time, I start with the DAX question. That is the easier option to develop and take a look at the expression.

I take the primary question and enclose it with the filter expression.

As I have to filter two tables (Retailer and Geography), I need to use FILTER() and RELATED():

EVALUATE
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZECOLUMNS(Retailer[Store], 'Geography'[Continent])
,"Retail_Sales", 'All Measures'[Retail Sales]
)
,FILTER(Retailer
,OR(CONTAINSSTRING('Retailer'[Store], "Contoso T") && RELATED(Geography[Continent]) = "Europe"
,CONTAINSSTRING('Retailer'[Store], "Contoso S") && RELATED(Geography[Continent]) = "North America")
)
)
ORDER BY [Retail Sales] DESC, 'Geography'[Continent], Retailer[Store]

I would like the RELATED() perform as I exploit FILTER() to iterate by the Retailer desk, and I would like the Continent column from the Geography desk.

Because the Geography desk is on the one aspect of the Relationship, I can use RELATED() to get the Continent column.

That is the consequence:

Subsequent, we should translate this filter to an RLS rule.

For the RLS rule, we will take away the FILTER() perform, because the RLS rule inherently works as a filter.

Observe that I eliminated the expression from the “Geography” desk.

Once I take a look at this rule in Energy BI, I get the next consequence, which corresponds to the consequence from the DAX question:

For testing the RLS rule, for instance, if you need solely to get the checklist of filtered shops, you possibly can write a easy question with simply the FILTER() perform:

Till now, we checked out static RLS guidelines.

However more often than not, we want guidelines based mostly on the Consumer-Login.

To attain this, we want a desk that maps the person to the rows the person wants entry to.

For instance, a desk like this:

After including the desk to the info mannequin, we have to add a Relationship between the brand new desk and the “Geography” desk:

The connection between the brand new “Geography Entry” desk and the “Geography” desk have to be configured accurately.

After including the Relationship, Energy BI configures it as a 1:n Relationship, with the “Geography” desk on the one aspect and the Filter flowing from the “Geography” desk to “Geography Entry”.

However we wish to filter the “Geography” desk based mostly on an RLS rule (a filter) on “Geography Entry”.
Because of this, we should change the cross-filter course to each:

As well as, we should set the flag on “Apply safety filter in each instructions,” as Energy BI ignores the cross-filter course setting when making use of RLS guidelines.

Now we will add the RLS rule:

Keep in mind to take away any filter expression on the Retailer desk earlier than including this rule.

When testing the RLS rule, I get this:

To seek out out what occurs, let’s return to the RLS rule editor and alter the view for the Rule to DAX:

The easy RLS rule editor doesn’t acknowledge DAX features and provides them as Textual content to filter.

We should change the expression to this:

Now the result’s as anticipated:

The Card on the top-left nook of the report web page incorporates a Measure with the USERPRINCIPALNAME() perform to make sure that the right person is lively throughout the take a look at.

I may even take a look at an RLS rule utilizing one other person:

It’s humorous that this person doesn’t have to exist. It solely must be contained within the “Geography Entry” checklist.

Right here is the results of the take a look at:

Within the yellow line on high, you possibly can see the lively person throughout the take a look at.

I confirmed you how one can create elementary RLS guidelines and how one can take a look at them.

Then I added extra complexity and analyzed the consequences of RLS guidelines on the underlying Storage engine.

We now have seen that the Components Engine processes a part of the RLS rule. Subsequently, we should write environment friendly code within the RLS guidelines.

Understanding how one can take a look at RLS guidelines earlier than implementing them within the knowledge mannequin is essential.

It’s a lot simpler to know unsuitable outcomes by understanding how the rule is utilized to the info mannequin.

Lastly, I added dynamic user-based RLS guidelines to the mannequin.

These guidelines are harder to check in a DAX question, as you need to know which knowledge every person can entry to jot down the right take a look at question to validate the consequence.

I hope I’ve given you some hints on simplifying your life with the RLS characteristic in Energy BI.

You’ll find a listing of Safety features in Energy BI on this Article:

You’ll find a easy clarification about Row Stage Safety in Energy BI on the Energy BI (Now Material) Group web page: Row-level security (RLS) with Power BI — Power BI | Microsoft Learn.

I can suggest this text by Nikola Ilic, the place you may get a place to begin about RLS:

One other good introductory article on Row-Stage-Safety in Energy BI by Elias Nordlinder:

Go to my Tales Checklist for extra data about the FILTER() function and how one can analyze DAX Query with DAX Studio.

I exploit the Contoso pattern dataset, like in my earlier articles. You’ll be able to obtain the ContosoRetailDW Dataset free of charge from Microsoft here.

The Contoso Knowledge could be freely used below the MIT License, as described here.