Issues with dynamic RLS and hybrid table

[souzarodrigo]

Hello community. I have a fact table with more than 100 million records, so I created two partitions in the semantic model: one in Import mode with a relative date range (hot partition) and another in DQ mode (cold partition), but I am having issues the RLS. Even though a query returns only 100k records, for example, I receive the 1 million limit error on my visuals. Tracing the queries submitted to the source, I noticed Power BI sends an additional query to the database without any filters and this specific query returns more than 1M rows. I believe it is related to RLS because this extra query returns a single column which is the column used as filter condition on dynamic RLS. Also, if I switch to static RLS or without RLS at all, it works perfectly. I could not find anything here or on documentation related to this so, I would like to know if anyone have ever faced similar issues and how to solve it. Thank you!

[v-sgandrathi · Accepted solution]

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.

[souzarodrigo]

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. 

[v-sgandrathi]

Hi @souzarodrigo,

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.

Thank you.

[v-sgandrathi]

Hi @souzarodrigo,

Thank you @lbendlin for your response to the query.

Dynamic RLS can create problems in hybrid tables due to how Power BI checks security rules before displaying data. When RLS is applied directly to a large fact table, particularly one with a DirectQuery partition, Power BI runs an extra background query to see which rows a user can access. This query isn't filtered by visuals, date slicers, or partitions, so it scans the entire fact table and only retrieves the column used in the RLS condition. If the table has millions of rows, this unfiltered check can go over the DirectQuery one million row limit, even if the final result is much smaller. That's why you might see a "1M row limit" error on visuals, even if the actual data returned is less.

This issue doesn't happen with static RLS or when RLS isn't used, since Power BI doesn't need this large security check. The problem is specific to dynamic RLS on fact-level fields. The recommended approach is to set up dynamic RLS on a small imported dimension table, such as a user access or security mapping table. Power BI then uses the relationship between this table and the fact table to filter data, avoiding a large security query. This setup prevents scanning the entire fact table and avoids hitting the 1M-row DirectQuery limit. It also follows best practices for security, improves performance, and ensures reliable hybrid table operation.

If needed, further details can be added about query folding, cardinality, or hybrid table storage modes.

Thank you.

[lbendlin]

so I created two partitions in the semantic model: one in Import mode with a relative date range (hot partition) and another in DQ mode (cold partition), 

I don't think that is possible. The Direct Query partition in a standard incremental refresh can only be the "hot"  TODAY()  UTC partition.

Did you mean to say aggregrations?