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.
Community SupportHi ziyab
Thank you for your question, and thanks to MFelix for the accurate and thoughtful response.
As highlighted, the combination of RLS, bidirectional relationships, and a composite model using DirectQuery can lead to excessive row expansion, triggering the 1M row limit. In addition to those points, we recommend reviewing the granularity of your fact table and considering import-mode aggregation tables to reduce query size.
Also, limiting high-cardinality columns in visuals and applying filters like Accounting Period at the report level can help control performance. For deeper analysis, tools like Performance Analyzer or Log Analytics (if connected) can provide visibility into query behavior.
Happy to help! If this addressed your concern, marking it as "Accepted Solution" and giving us "kudos" would be valuable for others in the community.
Thank you.
How can I get the query it throws? Error leads to no query in Performance Analyzer.
