Forum Discussion
RLS causing Resultset Greater Than 1000000 Rows Error
Hi ziyab,
To fix the issue, minimize bidirectional filters by keeping "Apply security filter in both directions" only between User Bridge Franchise & Location, and remove it from Business and other dimension tables.
Simplify the RLS DAX rule by avoiding CONVERT and using a direct comparison like
pk_dim_user[source_user_id] = "34510".
Optimize the bridge table by pre-filtering it upstream for active users or applying DAX-based security in measures. Create Import-mode aggregation tables to handle common report queries and limit DirectQuery operations.
Apply report-level filters early to restrict data volume. Optionally, use SQL Profiler on the Fabric Lakehouse SQL Endpoint to trace and analyze the query expansion during "View as Role."
I hope this helped! Feel free to ask any further questions. If this resolved your issue, please mark it as "Accept as Solution" and give us Kudos to assist others.
Thank you.
Super UserHi ziyab ,
This is related with the way the Users table impact the SQL query when you apply the user on top of your bridge table because of the joins that occur when you filter the tables trough the RLS.
What is the RLS rule you are using is it USERPRINCIPALNAME or something more complex?
