RLS causing Resultset Greater Than 1000000 Rows Error

[ziyab]

Hi,

I have a DQ fact table which works fine when you interact with the slicers. But when RLS is applied, it results into Resultset Greater Than 1000000 Rows Error for same slicer options. Data source is Fabric Lakehouse. Hot n Cold partitioning is also applied on the fact table.
Here is the model:

RLS is applied on the User table. Apply security filter in both directions is enabled between pk_bridge_user_location and Franchise & Location. Apply security filter in both directions is also between Business, and Franchise & Location. pk_bridge_user_location and Franchise & Location are joined using location_key. Location GL Category Rank by Business Units is the Fact Table which is linked with Franchise & Location using location_key.

In the table visual, I have columns from Franchise & Location, Chart of Accounts, Business, and Location GL Category Rank by Business Units. Slicers are from Franchise & Location, and Accounting Period. Filters are from Period, Business Unit, Chart of Accounts, and Accounting Period.

Again, all works fine before RLS does this:

What could be the issue here? Need your help. Thanks!

[v-sgandrathi · Accepted solution]

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.

[v-sgandrathi]

Hi  @ziyab,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If my answer resolved your query, please mark it as "Accept Answer" and give Kudos if it was helpful.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hi @ziyab,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If you've already resolved the issue, you can mark the helpful reply as a "solution" so others know that the question has been answered and help other people in the community. Thank you again for your cooperation!
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

[ziyab]

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!!

[MFelix]

Hi @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?

[ziyab]

For testing, i am simply passing a user_id. Static RLS.

[MFelix]

What I mean is what is the DAX code you are using for the RLS parametrization.

Apologies for not being explicit in my question.

[ziyab]

CONVERT(pk_dim_user[source_user_id],STRING) = "34510"

[v-sgandrathi]

Hi @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.

[ziyab]

How can I get the query it throws? Error leads to no query in Performance Analyzer.

[MFelix]

Hi @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

https://radacad.com/row-level-security-on-a-directquery-to-power-bi-dataset-composite-model-my-findi...

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.

[ziyab]

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.