Row level security

[DiKi-I]

Hi,

I've a measure which is calculated from one of the table which is in direct mode. I've craeted a measure which has row level security on employees. I also have another measure which show total but that is also getting filtered for the logged in employee. Is there a way so that I cannot have row level security on the total measure? The table is in direct query mode in power bi.

[v-sdhruv]

Hi @DiKi-I ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[ribisht17]

Hi @DiKi-I 

Step 1

Create an isolated fact table which can capture the Measure 

Step 2

Any DAX implementation suggested might not be effective because Row-Level Security takes precedence in the order of operations. As a result, changes to the context through DAX will not have an impact, since it operates on data that has already been filtered by the security layer, hence Step 1 is needed

Probably same suggestion as @lbendlin 

Regards,

Ritesh

Community Champion

Please mark the answer if helpful so that it can help others

Dance-Sing with Data Projects - YouTube

[v-sdhruv]

Hi @DiKi-I ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[v-sdhruv]

Hi @DiKi-I ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[PijushRoy]

Hi @DiKi-I 

CALCULATE(SUM(Table[Column]), ALL(Table))

The ALL() function removes all filters, including those imposed by RLS, and ensures the total is calculated across all rows in the table.

Leverage a disconnected table: Create a separate table or measure that is not tied to RLS settings. This disconnected table can hold the data for your total measure.

Use the REMOVEFILTERS function: If you want to keep the ability to selectively apply filters, you can use REMOVEFILTERS instead of ALL, which removes specific filters from your calculations while retaining others.

[rohit1991 · Accepted solution]

HI @DiKi-I ,

DirectQuery mode with Row-Level Security (RLS) applied, all measures—including totals—are inherently affected by the security filters unless explicitly handled. To bypass RLS for a specific measure like your total, you can create a separate measure using the ALL() or REMOVEFILTERS() function to ignore the RLS filter on the employee table. For example:

Total_Without_RLS = CALCULATE([YourMeasure], REMOVEFILTERS(EmployeeTable))

However, be aware that this will only work if the RLS is not enforced at the data source level (e.g., via SQL views or server-level security). If it is purely Power BI-managed RLS, this method should allow you to show unfiltered totals even in a DirectQuery model.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

[PijushRoy]

Hi @DiKi-I 

CALCULATE(SUM(Table[Column]), ALL(Table))
The ALL() function removes all filters, including those imposed by RLS, and ensures the total is calculated across all rows in the table.

Leverage a disconnected table: Create a separate table or measure that is not tied to RLS settings. This disconnected table can hold the data for your total measure.

Use the REMOVEFILTERS function: If you want to keep the ability to selectively apply filters, you can use REMOVEFILTERS instead of ALL, which removes specific filters from your calculations while retaining others.

[lbendlin]

Row Level Security is applied to rows, before your measure is calculated.  You would need to have two fact tables, one with RLS applied, and one anonymized without RLS.  Then you can use the second table for the totals.