Calculation Groups and RLS

I had a specific requirement to be able to show summary data for a whole organisation ignoring any RLS while being able to slice by all attributes. At the same time I needed to only show data attributed to names, for particular groups defined by RLS. There are many approaches to doing this. One I’d recommend looking at is: SQL BI Implement Non Visual Totals

The previously linked approach could have been made to work however the data model contained numerous fact tables already so the work involved would have been large. Instead I experimented with an approach that used TREATAS within a calculation group to pass filters selectively where needed.

Power Query Setup

For this example I start with a simple Person dimension table:

Referencing it twice and then setting the original to not load, lets us split it into two tables. The first will be where RLS gets applied. The second will be left open.

Model Setup

The full data model for this example then looks like:

Note the inactive relationship between (RLS) Name and Person. We’ll treat it as a disconnected table so it doesn’t matter, however I forced it to be 1 to Many in order to control the filter direction.

We can then create a basic demo report as follows:

1. Create a simple explicit measure in the Purchases table:

Sum of Amount = SUM ( Purchases[Amount] )

2. Define two simple RLS Roles:

3. Create a simple report to demo the behaviour:

You can see that because the relationship from the table ‘(RLS) Name‘ is not active, all the names show the total amount.

If we test as the Group A role it leaves the bar chart alone and filters the table. The numbers are however still wrong:

The numbers could be fixed by activating the relationship. However the bar chart then responds to the RLS as the filters propagate through the model.

We want a combination of the above with the bar chart ignoring the RLS and the table obeying it, but giving correct numbers. To do this we can turn the relationship back off and use a second measure that passes the filter manually using TREATAS.

(RLS) Sum of Amount = 
    CALCULATE (
        [Sum of Amount],
        KEEPFILTERS (
            TREATAS ( 
                VALUES ( '(RLS) Name'[PersonId] ), 
                Person[PersonId] 
            )
        )
    )

It works! Here TREATAS gets the VALUES of PersonId from ‘RLS Names‘ where RLS has been applied. Then applies them just for this measure to the Person table. In this simple example the KEEPFILTERS is not required but it’s there to prevent other filters being overwritten in more complex scenarios.

When experimenting I initially tried USERELATIONSHIP but you’re not allowed to use that in conjunction with RLS.

The above solution works well however requires every measure that you want to use it with to be duplicated. This is where we can leverage calculation groups!

Calculation Group Setup

Instructions and screen shots that follow are from Tabular Editor 3 but the same can be done in Tabular Editor 2.

1. Open Tabular Editor from the External Tools ribbon.
2. Right click Tables and click Create >> Calculation Group
   

3. I renamed mine to “RLS Calculation Group” and changed “Name” to “Item”.

4. Adding a calculation item called “Use Name Relationship” with the following DAX. It is the same as before but with the measure replaced with SELECTEDMEASURE( ).

CALCULATE (
    SELECTEDMEASURE( ),
    KEEPFILTERS(
        TREATAS(
            VALUES( '(RLS) Name'[PersonId] ),
            'Person'[PersonId]
        )
    )
)

5. Save and return to power bi.

6. The first time you return to Power BI you need to hit Refresh in order to recalculate the model.

7. Within Power BI we can now remove our duplicate measures.

8. Then for any visual which we want the RLS to apply we can drop the calculation group item into the Visual level filter and tick “Use Name Relationship”

It should be noted that we’re using a virtual relationship to pass the filters. Performance with a relatively large SCD person table has been really good except for a query involving distinct counts on a large fact tables. In this case creating a second calculation item that moves the filter direct onto the specific fact table fixed the issue.

Related to this while experimenting I came across “Security Filtering Behaviour = None” which is a property available in the Tabular model. It would appear that if you set the compatibility level to 1661 you can set it on one to many relationships. Sadly it currently has no effect apart from on Many to Many relationships. I’d love to know if Microsoft planned to develop it, as I think you could create some quite elaborate solutions.

I’d be really interested in your thoughts on this approach, any pitfalls I may have missed or whether you have other techniques to get the same result.

Example File

Demo file is available here: Example File

For reference Tabular Editors calculation group script is:

---------------------------------------------
-- Calculation Group: 'RLS Calculation Group'
---------------------------------------------
CALCULATIONGROUP 'RLS Calculation Group'[Item]

    CALCULATIONITEM "Use Name Relationship" = 
        CALCULATE(
            SELECTEDMEASURE( ),
            KEEPFILTERS(
                TREATAS(
                    VALUES( '(RLS) Name'[PersonId] ),
                    'Person'[PersonId]
                )
            )
        )