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.
I have tried converting all dimensional and bridge tables to Dual and turning on Assume Referential Integrity for inner joins. When I run the SQL query, it returns 3,895 rows. But in Power BI, it throws error of > 1M. What really baffles me is that I can use user as filter and there is no issue. The moment I hit View as Role, error!!
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?