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 - This information has enabled me to do a bit of a test, and it looks to me to be the way a filter is determined inside ISFILTERED( ). I have very basically replicated your scenario and hopefully the screenshots below explain, Note RLS is active in both screenshots. 

 

Firstly - using the DAX

SWITCH( TRUE(), ISFILTERED( 'Dimension'[Place] ), SUM( 'Fact'[Amount] ), 100001 )

The measure gives 10001 indicating that no filter is being passed to 'Dimension'[Place] via RLS, but as my final screenshot will show, there is a filter being passed to Fact. 

 

 

However, if we switch the DAX to:

SWITCH( TRUE(), ISFILTERED( 'Fact'[Place] ), SUM( 'Fact'[Amount] ), 100001 )

The measure returns the expected result.

 

Now I have very crudely placed the relationship on 'Fact'[Place] > 'Dimension'[Place], but it might present an option for how you can amend your DAX calculation. 

 

I am using your implementation of RLS, so I was wrong that this was the issue. 

 

 

Although with a change to the way your measure DAX works, you may be able use a simple rule and acheive the same result. 

 

 

Finally, this shows that RLS has been in place in all scenarios.

 

 

I'd imagine this is an over simplified example, but at least it shows where the issue is occuring and may help you reach a solution. 

MedIDRI's avatar
MedIDRI
Regular Visitor
1 year ago

Hello everyone mark_endicott Anonymous NimaiAhluwalia  ,

Thanks for all the reply!

Presentation of My Star Model and Context

I would like to present my star model that I use in Power BI. This model includes a fact table and several dimension tables, with a cardinality of one to many and a unidirectional filter from the dimension table to the fact table. I am using a SQL Server data source in import mode.

Implementation of RLS

I have implemented row-level security (RLS) with a simple static case : Dim Acteur'[CdVendeur] == "ABCD1111".

The RLS is applied at the level of my dimension table.

 

Issue with DAX Measures

I am encountering a problem with a DAX measure that uses the ISFILTERED and ISINSCOPE functions. When I integrate these functions into my measure, the RLS does not seem to apply correctly.

Here is an excerpt of my measure:

Po TCV RLS = SWITCH( TRUE(),
                            ISINSCOPE('Dim Acteur'[CdVendeur]) || ISFILTERED('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
                            )

Observations:

* The RLS applies correctly and filters the data well with the following measure:

Po TCV RLS 2 = 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"})
                            )

*When I use only CALCULATE without ISFILTERED or ISINSCOPE, the RLS works as expected.

*By adding these conditions, the measure consistently returns the default value (1000001), indicating that the RLS is no longer applying.

 

merci de trouver ci-joint un lien drive pour telecharger le PBIX avec le use case :

https://drive.google.com/file/d/1lnCeQdQ_wNW5WArJY8xoXcCYa5cr5QT-/view?usp=drive_link

  • 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. 

     

    • MedIDRI's avatar
      MedIDRI
      Regular Visitor
      1 year ago

      Thanks a lot mark_endicott !

      Your help with was invaluable and really helped me get unstuck.

      I appreciate your support!

    • 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!