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. 

     

MedIDRI's avatar
MedIDRI
Regular Visitor
1 year ago

mark_endicott 

Unfortunately none of the proposed solutions allowed me to resolve my problem.

 

With the same DAX code for RLS and the same relationships between the tables, the measure that includes ISFILTERED and ISINSCOPE returns incorrect results :

Po TCV RLS =SWITCH(TRUE(),
                     ISINSCOPE('Dim Acteur'[CdVendeur]) || ISINSCOPE('Dim Acteur'[LbVendeur]) || ISFILTERED('Dim Acteur'[CdVendeur]),
                    CALCULATE( SUM('Fait Vente'[MesureCasPondereTCV]),
                                        'Fait Vente'[TypeActeur] IN {"VRC", "VP"},
                                        NOT(LEFT('Fait Vente'[Code Fonction], 3) IN {"RDV", "RAV"})

                                       ),

                 1000001
)

 

While the measure that uses only CALCULATE returns the correct results.

Po TCV RLS =
                    CALCULATE( SUM('Fait Vente'[MesureCasPondereTCV]),
                                        'Fait Vente'[TypeActeur] IN {"VRC", "VP"},
                                        NOT(LEFT('Fait Vente'[Code Fonction], 3) IN {"RDV", "RAV"})

                                       )

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi MedIDRI ,

Thanks for all the reply!
And MedIDRI , What is your data source connection mode? ISFILTERED and ISINSCOPE are not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
ISFILTERED function (DAX) - DAX | Microsoft Learn
ISINSCOPE function (DAX) - DAX | Microsoft Learn

If this is not the reason, then perhaps we need a more detailed data model to determine the cause of the problem. Thank you!

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.