Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Row Level Security with Multiple levels (Complicated)

Hi,   I am looking to implement RLS for multiple levels. I have 2 tables as below. 1) Groups 2) Tasks Groups table will have , Group name and Member email. And tasks will have below ...
Need Help
Need Help DAX
power bi
RLS
RLS problems
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Is this the filtering expression you're after?

    Tasks[SubmittedBy] = USERPRINCIPALNAME ()
    || Tasks[IsRestricted] = "No"
    || Tasks[RestrictedAccess]
        IN CALCULATETABLE (
            VALUES ( Groups[GroupName] ),
            Groups[Members] = USERPRINCIPALNAME ()
        )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    mwegener thsi is really great. working out well. could you please explain me how you derive it? 

    thanks for your time.

    • mwegener's avatar
      mwegener
      Icon for Most Valuable Professional rankMost Valuable Professional
      4 years ago

      Hi Anonymous, I think the 3 roles align very well with your 3 rules and the split helps simplify the requirements. Rule 1 and 3 are quite simple as the criteria are already present in the Tasks table. Rule 2 is a typical pattern when assigning groups.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        mwegener I agree, Rule 2 is little tricky.

        can you please help me understand the DAX we used for Rule 2?

        CONTAINS( FILTER(Groups, Groups[Members] = USERPRINCIPALNAME() ), Groups[GroupName] , [RestrictedAccess], the same DAX expression when i add it as a measure it is throwing an error but the same is working in Roles.How do you know that this expression will work in Roles?we usually test the DAX in a measure and then we copy the same to Roles right then how did you know thaqt this will work? please answer me