Forum Discussion
Issues with dynamic RLS and hybrid table
Hi souzarodrigo,
Thank you for your detailed update. You are correct, when dynamic RLS is used with a small security mapping table, Hybrid (Import + DirectQuery) partitions can still cause a full DirectQuery scan if a visual includes a field from the fact table. This happens because the engine checks row-level permissions across all partitions, and if RLS could impact rows in the DirectQuery part, Power BI sends an extra security validation query to the DirectQuery partition, regardless of the user’s filters.
This means dynamic RLS can still hit the 1-million-row DirectQuery limit. This is a known limitation of dynamic RLS with Hybrid tables, not a modelling issue. Static RLS works since the model resolves security at design time, avoiding the extra query. If possible, moving dynamic RLS to the source database is the most reliable workaround for large Hybrid tables. Other options include applying sensitive filters to fully imported dimensions or avoiding fact-level fields in the RLS logic. Thanks for sharing your findings, they’ll be helpful for others in similar situations.
Thank you.
Hi v-sgandrathi and lbendlin, Thank you so much for your inputs here. I tried with your suggestion for best practices and apply the RLS to a smaller table, but without success. I used dynamic RLS with a security mapping table that relates to my users table, both in Dual mode, related to the fact table which is in Hybrid mode (simple star schema, 1 to many relationships). I was expecting the relationship would filter the data in fact table for both partitions resolving for either for the DQ query or the Vertipac query, but seems that if I add any field from the fact in the visual, it generates the additional DQ query. Seems to be a limitation on Hybrid tables security. Everything works perfectly with static RLS. We will test also moving the RLS to the source DB and evaluate on how to proceed. Thank you again.