Forum Discussion
Row level security
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.
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.
8 Replies
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.
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.
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.
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.
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
