Forum Discussion
DAX Code for Dynamic RLS
sorry, was a bit quick there, you need to write it like this
VAR _t = CALCULATETABLE ( VALUES ( 'Access_Table'[Access_Required] ), FILTER ( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME () ) ) RETURN IF ( CONTAINS ( _t, 'Access Table'[Access_Required], "National" ) || CONTAINS ( _t, 'Access Table'[Access_Required], "Regional" ), 'dimCompany'[Company] IN CALCULATETABLE ( VALUES ( 'Access_Table'[Company] ), FILTER ( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME () ) ), TRUE () )
IF(
CONTAINS(
CALCULATETABLE(
VALUES( 'Access_Table'[Access_Required] ),
FILTER( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME() )
),
'Access Table'[Access_Required], "National" || 'Access Table'[Access_Required], "Regional"
),
'dimCompany'[Company]
IN CALCULATETABLE (
VALUES( 'Access_Table'[Company]),
FILTER('Access_Table','Access_Table'[User_Email] = USERPRINCIPALNAME() )
),
TRUE ()
)
Resident Rockstarsorry, was a bit quick there, you need to write it like this
VAR _t =
CALCULATETABLE (
VALUES ( 'Access_Table'[Access_Required] ),
FILTER ( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME () )
)
RETURN
IF (
CONTAINS ( _t, 'Access Table'[Access_Required], "National" )
|| CONTAINS ( _t, 'Access Table'[Access_Required], "Regional" ),
'dimCompany'[Company]
IN CALCULATETABLE (
VALUES ( 'Access_Table'[Company] ),
FILTER ( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME () )
),
TRUE ()
)- jdbuchanan71
Super User
Well I know what I am studying today! :smileyhappy:
Nicely done sturlaws, I updated my testing file with your new code.
Would like to know, what if a column should retuen all vlaues from rRequired Region when it is blank? it should consider all regions if i keep it blank. Pls suggest
