Forum Discussion

MedIDRI's avatar
MedIDRI
Regular Visitor
1 year ago
Solved

Issue with RLS and DAX Measures

Hello everyone, I am facing an issue with a DAX measure in Power BI that uses the ISFILTERED and ISINSCOPE functions. I have implemented row-level security (RLS) based on the email address of the lo...
Need Help
  • mark_endicott's avatar
    mark_endicott
    1 year ago

    MedIDRI - you could use HASONEFILTER ( 'Dim Acteur'[CdVendeur] 

     

     

     

    SWITCH (
    TRUE (),
    HASONEFILTER ( 'Dim Acteur'[CdVendeur] ),
        CALCULATE (
            SUM ( 'Fait Vente'[MesureTCV] ),
            'Dim Acteur'[TypeActeur] IN { "VRC", "VP" },
            NOT (
                LEFT ( 'Dim Acteur'[Code Fonction], 3 )
                    IN { "RDV", "RAV", "DAV", "DC", "DDV", "DAE" }
            )
        ),
    1000001
)

     

     

     

     

     
    I do understand that this will not work if users have access to more than one CdVendeur though. 

     

    Possibilities are this is because ISFILTERED expects a direct filter on the column rather than one from RLS, it's due to the order of the queries (ie. where RLS is applied), it's by design OR we've found a bug with ISFILTERED 

     

    ( marcorusso - do you have any knowledge on why ISFILTERED returns false when filtered through RLS but HASONEFILTER returns true? ) 

     

     

    MedIDRI - ISINSCOPE will not work in this context because with a card 'Dim Acteur'[CdVendeur] is not in the context of the visual. 

     

    I've put a few more things in your file to help explain this. See attached. 

     

mark_endicott's avatar
mark_endicott
Super User
1 year ago

MedIDRI - you could use HASONEFILTER ( 'Dim Acteur'[CdVendeur] 

 

 

 

SWITCH (
    TRUE (),
    HASONEFILTER ( 'Dim Acteur'[CdVendeur] ),
        CALCULATE (
            SUM ( 'Fait Vente'[MesureTCV] ),
            'Dim Acteur'[TypeActeur] IN { "VRC", "VP" },
            NOT (
                LEFT ( 'Dim Acteur'[Code Fonction], 3 )
                    IN { "RDV", "RAV", "DAV", "DC", "DDV", "DAE" }
            )
        ),
    1000001
)

 

 

 

 

 
I do understand that this will not work if users have access to more than one CdVendeur though. 

 

Possibilities are this is because ISFILTERED expects a direct filter on the column rather than one from RLS, it's due to the order of the queries (ie. where RLS is applied), it's by design OR we've found a bug with ISFILTERED 

 

( marcorusso - do you have any knowledge on why ISFILTERED returns false when filtered through RLS but HASONEFILTER returns true? ) 

 

 

MedIDRI - ISINSCOPE will not work in this context because with a card 'Dim Acteur'[CdVendeur] is not in the context of the visual. 

 

I've put a few more things in your file to help explain this. See attached. 

 

marcorusso's avatar
marcorusso
Most Valuable Professional
1 year ago

Security is security: the user within RLS doesn't know (and must not know) whether there is other data outside the security perimeter defined by RLS. At all effects, RLS defines a subset of the database, but you have to imagine you have a copy of the model with just the data the user can see. Therefore, there are no filters applied and if a table has only one row, there is only one row - but no filters.

  • mark_endicott's avatar
    mark_endicott
    Super User
    1 year ago

    marcorusso - Thanks for your input! Really helps me understand why one works but the other doesn't!