How to perform calculations involving overall values under RLS constraints

[v-jingzhan-msft]

Scenario: 

In some scenarios, we need to use RLS to restrict user’s access to certain data, but this can conflict with certain calculations involving overall data, such as rankings. In this article, I will show you how to achieve this.

Sample Data:

In the sample data, there are two tables.

Table 1 is the score table, including employee and channel.

Table 2 is the employee table, including the email address table of employee.

How:

If we use RLS directly on tables that involve the calculation of overall values, the result is often wrong.

For example: We ranked the scores of our employees and the results are as follows.

Here is the Rank measure:

Rank = 
RANKX (
    FILTER ( ALL ( 'Score' ), 'Score'[Channel] = MAX ( 'Score'[Channel] ) ),
    'Score'[Score],
    MAX ( 'Score'[Score] ),
    DESC,
    DENSE
)

If we use RLS directly, we are constrained by RLS and we cannot access all the values. Therefore the result is not correct.

The following are the steps to set up RLS:

1.From the Modeling tab, select Manage Roles.

2.From the Manage roles window, select Create.

3.Under Roles, provide a name for the role.

4.Under Tables, select the table to which you want to apply a DAX (Data Analysis Expression) rule.

5.In the Table filter DAX expression box, enter the DAX expressions.

After you've created your roles, test the results of the roles within Power BI Desktop.

1.From the Modeling tab, select View as.

2.The View as roles window appears, where you can see the roles you've created. Select a role you created. Then choose OK to apply that role.

3.Select OK.

This is our expected result:

According to our description, here are my steps we can follow as a solution.

1.Create a new table to have the overall ranking data:

Summarize Score = SUMMARIZE('Score',Score[Score],Score[Channel])

Note: Please avoid using this table in any other place since it could expose RLS data.

2.Since the summary table is only used for DAX calculations and not for creating any visuals, we hide it:

Note: There is no relationship between the summary table and other tables.

3.Create RLS in Table 2(Table Employee)

4.Since there is no relationship between the Summary table and other tables, it will not be affected by the RLS, while the RLS of the Employee table will be passed to the Score table, which will cause the Score to retain the data after being restricted by the RLS.

Now you can rank the scores of the employees, here is the measure:

Rank Employee = 
VAR _a =
    FILTER ( ALL ( 'Summarize Score' ), [Channel] = MAX ( 'Score'[Channel] ) )
VAR _b =
    ADDCOLUMNS ( _a, "Rank", RANKX ( _a, [Score] ) )
RETURN
    MAXX ( FILTER ( _b, [Score] = MAX ( 'Score'[Score] ) ), [Rank] )

Summary:

In some cases that involve the calculation of overall values but require the use of RLS, you can use a method like this to perform the calculation .

Hope this article will help people with similar questions.   

Author: Neeko Tang

Reviewer: Ula and Kerry

How to perform calculations involving overall values under RLS constraints.pbix