Solved: RLS over Direct Query

Ondrej_NOVAK · ‎05-04-2026

Hello,
I am creating report with basic imported info and detailed pages getting data using direct query.
I have "Central" Imported table PARTY with basic party informtion. And I have attached two direct query tables with details (eg invoices and contact history) on Oracle database using two different connections. All three has common PARTY_ID column that I use for relationship (PARTY-DQ1 and PARTY-DQ2, both 1:N relationships, PARTY serves are imported dimension with two DQ fact tables).
The idea is that main page contains basic data from PARTY. When I need to see details it is for one party only (so PARTY table is filtered to one row, eg by DTD) and it works great, because SQL for Direct Query is generated with filter for this one selected PARTY_ID.

The issue is when I add any RLS on PARTY (eg. user can see only his customers, etc.), the Direct Query SQL breaks, it starts to load whole DQ source and ignores filtered PARTY_ID, which is not feasible because of performance issues (these are long and detailed). I guess RLS is interpreted first and filter on PARTY_ID would be applied later when it is loaded, but I have no idea.

Do you have any idea how to combine RLS (which typically returns a lot of PARTY rows) with detailed filter to one PARTY for PBI to use both in one sql?

Ondrej_NOVAK

That sadly is not an option, not every report user has access to database.

But I have found a workaround:
I can bind parameter with the filtered value of imported table and modify Direct Query sources to filter to this single value using the parameter (in Power Query, or even in custom SQL), this way I get optimized SQL filtered to one value and RLS capability to make sure that only allowed information is displayed.

View solution in original post

v-pnaroju-msft

Hi @Ondrej_NOVAK,

Thank you for the update and for sharing the details of the workaround, which will benefit other community members who may be facing similar issues.

Please continue to use the Microsoft Fabric community for any further queries.

v-pnaroju-msft

Thankyou, @FBergamaschi and @cengizhanarslan for your responses.

Hi @Ondrej_NOVAK,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solutions provided by @FBergamaschi and @cengizhanarslan to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

cengizhanarslan · ‎05-05-2026

Since your DQ tables are on Oracle, the most reliable fix is to implement source-level security directly in Oracle rather than relying on Power BI RLS to filter the Import-to-DQ boundary. Power BI passes the authenticated user's credentials through to Oracle when using SSO-enabled DirectQuery connections, so the filtering happens at the SQL generation level before any rows are returned.

_________________________________________________________
If this helped, ✓ Mark as Solution | Kudos appreciated
Connect on LinkedIn | Follow on Medium
AI-assisted tools are used solely for wording support. All conclusions are independently reviewed.

Ondrej_NOVAK · ‎05-04-2026

Hi @FBergamaschi

sorry for the confusion.

Yes, dimension PARTY is imported, two direct query fact tables are connected to it and it works as intended:

Detailed page over DQ1 is using data from DQ1, and is filtered by PARTY.PARTY_ID (by DTD). Power BI sends select SQL statement over DQ1 with filter PARTY_ID = selectedparty. I guess it filters the imported table and using relationship it generates filtered SQL for DQ to fetch only datat that are required.

Use Case: show me all invocies of selected party

But when I add RLS to dimension PARTY (nothing else changes), it starts fetching all DQ data that are suitable for RLS, ignoring PARTY_ID filter. SQL in database is same, but without filter on PARTY_ID. It ends with timeout error (or too many rows error depends on the case) ... and my guess is that it tries to apply RLS first in SQL and apply standard filter later, while I need it to apply both in the SQL.

So I would expect PBI to "show me all invoices of selected party and show it to me only if I can see the party" but instead I think it does this "get all invoices of all parties the user can see and then filter it to selected party", which takes forever 😕

Is it easier to understand now?

FBergamaschi · ‎05-04-2026

Yes now it is clearer

So

1 - you apply RLS in Power BI Desktop

2 - you are getting errors (time out or out of memory) in Power BI Desktop / Cloud

Are 1 & 2 correct ?

If yes, do you get the error both in Dekstop and in Cloud or only in one of the two?

And just to understand, how many columns are rows are the table made of?

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Ondrej_NOVAK · ‎05-04-2026

@FBergamaschi yeah, and the reson seems to be then before I apply RLS I can see SQL from Power BI to be filtered to one selected PARTY_ID, but after I apply RLS, the Power BI tries to load data unfiltered by PARTY_ID.
It is actually working fine on Desktop (using Show As functionality) but breaks after publishing to Cloud.

Gazillion rows give or take 😄 thats why filtering it to one PARTY_ID is critical, I just need to make sure that user can see selected PARTY_ID.

FBergamaschi · ‎05-05-2026

OK so in this case can you implement RLS at the source database level? That should fix the thing. When th euser logs, PBI will pass the credentials to the DB

Let me know if this can work

Thanks