Forum Discussion

alfBI's avatar
alfBI
Responsive Resident
9 months ago
Solved

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 use...
Need Help
  • alfBI's avatar
    alfBI
    9 months ago

    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

alfBI's avatar
alfBI
Responsive Resident
9 months ago

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

v-hashadapu's avatar
v-hashadapu
Community Support
9 months ago

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.