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 ,
There may be a couple of questions concerning your issue the fact that you have a composite model may have impact on the result because import tables work in a different way from the direct query ones on this type of models.
Check this post to start
Also I believe the bidirectional filters may be causing some problem, since the tables are all joined by a one to many then when you filter the user table the Franchise & Location will be filtered accordingly that will force the filter to the rest of the model.
Just giving some pointers because it's difficult to give a proper answer just based on an image specially for RLS and composite models.
Thanks for your reply.
RLS has to be on the User which then filters everything. Bidirectionals are required so that other dimensions can be filtered. I have tried converting user, bridge, and other tables to Dual but still no luck. Also the visual throws error, so you cannot get the query from Performance Analyzer.