Forum Discussion
RLS Expression Evaluation Failed - Failed connection
Hi everyone,
We are implementing a RLS on a semantic model on a Table (e.g: Table A) . This RLS requires to check if a given row exists on a table (we will name TableRLS) in orde to decide if user can see something or not.
The DAX expression to implement the RLS looks like that
VAR AllowedDocs =
SELECTCOLUMNS(
FILTER(
'TableRLS',
LOWER('TableRLS'[EmailAddress]) = LOWER([FixPBIUsername])
),
"DocID", 'TableRLS'[Document UID]
)
RETURN
IF(
TableA[Has RLS ?] = TRUE(),
TableA[Document UID] IN AllowedDocs,
TRUE()
)
Due to the huge size of TableRLS (about 200M rows) this table is consumed using Direct Query. Table A is imported.
Both tables are stored on a Fabric Datawarehouse
So, when we test the reports based on this model using the role with the RLS implemented we notice the following error intermittenly
An error was encountered during the evaluation of the row level security expression defined on table '<oii>TableA/oii>'.
Error message: An error was encountered during the evaluation of the row level security expression defined on table '<oii>TableA</oii>'.
Error message: The operation was cancelled by the user. A connection could not be made to the data source with
the Name of '{"protocol":"tds","address":{"server":"XXXXXXXXX.datawarehouse.fabric.microsoft.com","database":"Star"},
"authentication":null,"query":null}'.
We have rulld out permission or authentication problems as the rules some times works fine. Since issue are intermitetnt it looks like more related to some problem of performance when the Power BI service tries to complete the query on the TableRLS on the Fabric DWH.
Any idea about how to handle that?
Regards,
Alfons
Hi,
By the way it looks like we have been able to manage the issue applying a less drastical approach that implied:
1) Optimize DAX RLS Expression
E.g:Before
VAR AllowedDocs = SELECTCOLUMNS( FILTER( 'RLS_Document', LOWER('RLS_Document'[EmailAddress]) = LOWER([FixPBIUsername]) ), "DocID", 'RLS_Document'[Document UID] ) RETURN IF( DimDocVersion[Has RLS ?], DimDocVersion[Document UID] IN AllowedDocs, TRUE() )After
IF ( NOT DimDocVersion[Has RLS ?], TRUE(), CALCULATE ( COUNTROWS ( FILTER ( 'RLS_Document', 'RLS_Document'[Document UID] = DimDocVersion[Document UID] && 'RLS_Document'[EmailAddress] = [FixPBIUsername] ) ) ) > 0 )2) Implement Query Caching and Large semantic model (oour model has a size about 1 GB, so its a good candidate)
After these changes no more errors has been raised. Let's see if this is the final solution or issue re-appears. In such a case we will towards change the model import+directquery towards a import+direct lake.
Regards
5 Replies
Hi alfBI , Thank you for reaching out to the Microsoft Community Forum.
Your RLS is intermittently failing because the role’s DAX forces the Power BI/Fabric engine to run a large DirectQuery lookup against a 200M row table during security evaluation, those remote scans are sometimes slow and get cancelled by the service (hence the “operation was cancelled by the user / connection could not be made” message). Per row filters that call FILTER( 'TableRLS', ... ) or use LOWER() block index use and produce many expensive queries, so the engine can’t reliably complete them under load.
Remove the per row DirectQuery dependency from the RLS path. Materialize a small mapping used for security (import a reduced Email -> DocumentUID table or nightly-refreshed subset) and base the RLS on that imported table or a simple relationship/TREATAS/EXISTS filter so the engine can translate the rule into one fast SQL operation. If import isn’t possible, create a precomputed, indexed/partitioned view or table in the warehouse (stored lowercase keys so you don’t need LOWER() in DAX), add proper indexing/statistics and convert the RLS to use that object, this reduces query time and eliminates the intermittent cancellations. I’m confident this approach will resolve the instability.
Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Learn
DirectQuery model guidance in Power BI Desktop - Power BI | Microsoft Learn
Performance Guidelines - Microsoft Fabric | Microsoft Learn
Statistics - Microsoft Fabric | Microsoft Learn
Row-level security (RLS) with Power BI - Microsoft Fabric | Microsoft Learn
Hi v-hashadapu.,
Thanks for your comments.
Just a question: With regards your idea anbout to create a indexed/partitioned view or table in the warehouse (Fabric)?Neither indexes or partitions are not currently supported on Fabric Warehouses.....
Tables - Microsoft Fabric | Microsoft Learn
We will try to move the RLS table to a Lakehouse (where partition is supported) and assess if a composite model mixing direct lake + import tables works.
Alfons
Hi alfBI , Thank you for reaching out to the Microsoft Community Forum.
You’re correct, Fabric Warehouse doesn’t support indexes or table partitions, so suggesting an indexed/partitioned view there wasn’t right; I was thinking of a traditional SQL warehouse, sorry about that.
Moving the RLS table to a Lakehouse Delta table and using partitions will enable pruning and give you more predictable Direct Lake lookups and pairing that with a composite model (Direct Lake for the RLS table + Import for the rest) is indeed the right approach to avoid per-row DirectQuery scans and intermittent cancellations.
Hi alfBI , Hope you are doing well. Kindly let us know if the issue has been resolved or if further assistance is needed. Your input could be helpful to others in the community.
Hi,
By the way it looks like we have been able to manage the issue applying a less drastical approach that implied:
1) Optimize DAX RLS Expression
E.g:Before
VAR AllowedDocs = SELECTCOLUMNS( FILTER( 'RLS_Document', LOWER('RLS_Document'[EmailAddress]) = LOWER([FixPBIUsername]) ), "DocID", 'RLS_Document'[Document UID] ) RETURN IF( DimDocVersion[Has RLS ?], DimDocVersion[Document UID] IN AllowedDocs, TRUE() )After
IF ( NOT DimDocVersion[Has RLS ?], TRUE(), CALCULATE ( COUNTROWS ( FILTER ( 'RLS_Document', 'RLS_Document'[Document UID] = DimDocVersion[Document UID] && 'RLS_Document'[EmailAddress] = [FixPBIUsername] ) ) ) > 0 )2) Implement Query Caching and Large semantic model (oour model has a size about 1 GB, so its a good candidate)
After these changes no more errors has been raised. Let's see if this is the final solution or issue re-appears. In such a case we will towards change the model import+directquery towards a import+direct lake.
Regards