RLS problems with Direct Query

[PedroFarias]

Hi folks,

I have a report that had a table connection recently changed to Direct Query, since then some of my RLS functions are not working as they should and I keep getting the "CONTAINSROW is not allowed on Direct Query". But here is the catch, I don't have any RLS functions that use CONTAINSROW nor anything similar to it, what I do have is something similar to:

[VENDOR] == "Vendor 1"

With that, I assign the Vendor to its function, and it usually works, but since the change some of the users are receiving the error that I mentioned. Does anyone know what can be the error?

Here is the list os the things that I already tried:

• Assigned some new users to the same function, the new users do not receive the same error.
  
  
• Tried to clear the cache for the users with error but had no effect.
  
  
• Asked a user to connect using my machine, also no effect.

The only difference between these users is that the users receiving the error are using the "Premium per User" license, and the ones that do not receive the error use the "Pro" license,  but I also tried to swap their licenses and had no effect.

[rajendraongole1 · Accepted solution]

Hi @PedroFarias  - The issue you're experiencing with CONTAINSROW and Direct Query after switching the connection mode, despite not explicitly using the function in your RLS (Row-Level Security), is likely caused by an indirect relationship between how Power BI interprets your RLS expressions and the limitations of Direct Query.

If [VENDOR] is coming from a related table (e.g., via a one-to-many or many-to-many relationship), ensure that the relationship is working as expected and does not introduce ambiguity.
For Direct Query, avoid bi-directional relationships or relationships involving calculated columns.

alternative:

If possible, switch back to Import mode, as it’s more forgiving with RLS and DAX expressions.
Implement RLS in the Source:

For Direct Query, consider implementing RLS directly in the source system (e.g., SQL Server, Azure SQL) to avoid Power BI limitations.
Split the Dataset:

Use a hybrid approach where only high-velocity data resides in Direct Query and other data is in Import mode. This can reduce the complexity of DAX expressions.

[v-saisrao-msft]

Hi @PedroFarias,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-saisrao-msft]

Hi @PedroFarias,

Thanks for reaching out to the Microsoft forum community.

Could you please confirm if your query have been resolved? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you.

[Poojara_D12 · Accepted solution]

Hi  @PedroFarias 

The "CONTAINSROW is not allowed on DirectQuery" error might be related to licensing differences between Premium Per User (PPU) and Pro licenses, as PPU users can face limitations with DirectQuery and RLS. Even though you're not using CONTAINSROW explicitly, Power BI might be generating queries that trigger unsupported operations in DirectQuery mode.

To fix the issue:

1. Test with Pro users to see if the error persists (PPU may have limitations).
2. Simplify your RLS logic to avoid complex DAX functions.
3. Ensure dataset refreshes are successful and RLS is correctly applied.
4. Check if there are issues with the DirectQuery connection settings or data model.

   If it still doesn't work, consider contacting Microsoft support for further troubleshooting.

[v-saisrao-msft]

Hi @PedroFarias,

Thanks for reaching out to Microsoft forum community.

In addition to the helpful response provided by @rajendraongole1 , could you please confirm if your query have been resolved? If so, please accept it as a solution and leave a "Kudos" so other members can find it more easily.

Thank you.

[SaiTejaTalasila]

Hi @PedroFarias ,

RLS applies to only viewer role it won't apply to any other roles.

Or you can remove RLS and try to test for the users it might give some more details.

Thanks,

Sai Teja 

[rajendraongole1 · Accepted solution]

Hi @PedroFarias  - The issue you're experiencing with CONTAINSROW and Direct Query after switching the connection mode, despite not explicitly using the function in your RLS (Row-Level Security), is likely caused by an indirect relationship between how Power BI interprets your RLS expressions and the limitations of Direct Query.

If [VENDOR] is coming from a related table (e.g., via a one-to-many or many-to-many relationship), ensure that the relationship is working as expected and does not introduce ambiguity.
For Direct Query, avoid bi-directional relationships or relationships involving calculated columns.

alternative:

If possible, switch back to Import mode, as it’s more forgiving with RLS and DAX expressions.
Implement RLS in the Source:

For Direct Query, consider implementing RLS directly in the source system (e.g., SQL Server, Azure SQL) to avoid Power BI limitations.
Split the Dataset:

Use a hybrid approach where only high-velocity data resides in Direct Query and other data is in Import mode. This can reduce the complexity of DAX expressions.