Issue with RLS and DAX Measures

[MedIDRI]

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 logged-in user, but when I incorporate these functions into my measure, the RLS does not seem to apply correctly.

Here’s an excerpt of my measure:

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
)

Here’s an excerpt of my RLS DAX :

[CdVendeur] IN SELECTCOLUMNS(
                                                       FILTER('Dim Acteur', 'Dim Acteur'[Email] = USERPRINCIPALNAME()),
                                                       "cdvendeur", 'Dim Acteur'[CdVendeur])

Observations:

• 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 applied.
• I have checked the relationships between my tables and I am confident they are correctly configured.

Questions:

Has anyone encountered a similar issue? Are there any solutions or best practices for using ISFILTERED and ISINSCOPE while still respecting RLS rules?

Thank you in advance for your help!

[MedIDRI]

Hello @v-junyant-msft 

Thanks for your reply 

I am using a SQL Server data source in import mode, and I have a star schema model with a 1-to-many cardinality, with single-direction filtering from the dimension table to the fact table. The RLS I created is at the level of my dimension table, and the column I apply the filter on is the same one I use in ISFILTERED and ISINSCOPE within my measure.

Best Regards,

[mark_endicott]

@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]

Hello everyone @mark_endicott @v-junyant-msft @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 · Accepted solution]

@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]

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]

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

[MedIDRI]

Thanks a lot @mark_endicott !

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

I appreciate your support!

[MedIDRI]

@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"})

                                       )

[mark_endicott]

@MedIDRI - if you are using an RLS rule like you are with DAX, then you should not need relationships becuase your DAX rule creates a virtual filter.

This indicates to me that either your DAX RLS rule is being applied on the wrong table, or the relationships are cancelling it out.

If your measure works when you just use the CALCULATE section then this only indicates that the measure returns the correct results.

It does not indicate that ISINSCOPE or ISFILTERED are not compatible with RLS - which they are (except on a DQ table as @v-junyant-msft points out). 

And to me, this indicates that your RLS rule itself, or the implementation of it is not working as desired. 

If you can share your PBIX or some sample data and a diagram of your model (to enable me to replicate it), I can investigate further. But without any of this, no further suggestions can be made. 

[v-junyant-msft]

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.

[mark_endicott]

@MedIDRI - If you find the solution with an answer please Accept it as the Solution, it helps others with the same challenge.

[MedIDRI]

Hello @mark_endicott 

Thanks for your feedback!

I don't think the problem is in the RLS DAX because to the same extent if I keep only the Calculculate I find the right result

I tested with this simple RLS : 'Dim Acteur'[Email] == USERPRINCIPALNAME()

And I still don't have the right result

[mark_endicott]

@MedIDRI - In my experience both ISFILTERED and ISINSCOPE work perfectly with RLS. I think you need to check your RLS rule, because the way you are using SELECTCOLUMNS doesnt look right to me. 

You could consider re-writing it to something along the lines of the below, but without any more detail of the model and the result you are trying to achieve, it's not clear if this will work:

VAR vendeur_filter =
    CALCULATETABLE (
        VALUES ( 'Dim Acteur'[CdVendeur] ),
        'Dim Acteur'[Email] = USERPRINCIPALNAME ()
    )
RETURN
    [CdVendeur] IN vendeur_filter

[NimaiAhluwalia]

Hello @MedIDRI 

Function Behavior with RLS Use Case

ISFILTERED	Operates within RLS	Check if a specific column has any filter applied (e.g., slicers, RLS).
ISINSCOPE	Operates within RLS	Check if a column is part of the grouping or hierarchy context.

I reckon working with this filters in the past with RLS and it was working fine for me.