Forum Discussion
Issue with RLS and DAX Measures
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.
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:
Observations:
* The RLS applies correctly and filters the data well with the following measure:
*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
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
)
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.
Thanks a lot mark_endicott !
Your help with was invaluable and really helped me get unstuck.
I appreciate your support!
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.
marcorusso - Thanks for your input! Really helps me understand why one works but the other doesn't!
