Forum Discussion

IzBell's avatar
IzBell
Frequent Visitor
1 year ago

Row Level Security based on organisation structure - multiple conditions

Hi Power BI Community,

 

I need some guidance on setting up Row-Level Security. I have two tables as below.
RLS table:

Employee IDEmailView All?Org UnitTeamDepartmentCountryOrg Unit - ExclusionTeam - ExclusionDepartment - ExclusionFunction - ExclusionRole - Exclusion
123[email protected] BrandMarketing Canada     
123[email protected] BrandMarketing USA     
456[email protected]Y         
789[email protected] BrandMarketing Canada   HRHR Manager
789[email protected] BrandMarketing USA   HRHR Manager
345[email protected] InnovationTechAI      
578[email protected] InnovationTechAI    HRHR Manager
255[email protected] InnovationTech    AI  
190[email protected] DCRetail Singapore   HRHR Manager
285[email protected] DC    Retail HRHR Manager

 


Data table:

Employee IDAmountOrg UnitTeamDepartmentCountryFunctionRole
101100BrandMarketingCommCanadaHRHR Manager
102100BrandMarketingCommUSAFinanceFinance Manager
103100BrandMarketingCommSingaporeITIT Manager
104100InnovationTechAICanadaHRHR Manager
105100InnovationTechAIUSAFinanceFinance Manager
106100InnovationTechAISingaporeITIT Manager
107100DCRetailStoreCanadaHRHR Manager
108100DCRetailStoreUSAFinanceFinance Manager
109100DCRetailStoreSingaporeITIT Manager
110100DCCIPackagingCanadaHRHR Manager
111100DCCIPackagingUSAFinanceFinance Manager
456100DCCIPackagingSingaporeITIT Manager

 

 

Here's what I want to achieve with RLS:

  1. Users with the "View All?" flag set to "Y" should have access to all data.
  2. Users with specific columns filled in should only see the corresponding data, except when the "Exclusion" column is filled.
  3. Users should not be able to see their own records.
  4. Users who do not meet any of these conditions should not see any data.

Your help would be much appreciated.

 

Many thanks,

IzBell

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi IzBell ,

    Please try the formula below:

    VAR CurrentUser = USERPRINCIPALNAME()

 // Read the current user’s values from the RLS table
 VAR ViewAllFlag =
  CALCULATE( MAX( 'RLS'[View All?] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )

 VAR RLS_EmployeeID =
  CALCULATE( MAX( 'RLS'[Employee ID] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )

 VAR AllowedOrgUnit =
  CALCULATE( MAX( 'RLS'[Org Unit] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )
 VAR ExcludedOrgUnit =
  CALCULATE( MAX( 'RLS'[Org Unit - Exclusion] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )

 VAR AllowedTeam =
  CALCULATE( MAX( 'RLS'[Team] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )
 VAR ExcludedTeam =
  CALCULATE( MAX( 'RLS'[Team - Exclusion] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )

 VAR AllowedDepartment =
  CALCULATE( MAX( 'RLS'[Department] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )
 VAR ExcludedDepartment =
  CALCULATE( MAX( 'RLS'[Department - Exclusion] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )

 // (Similarly do for Function and Role if needed)
 VAR ExcludedFunction =
  CALCULATE( MAX( 'RLS'[Function - Exclusion] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )
 VAR ExcludedRole =
  CALCULATE( MAX( 'RLS'[Role - Exclusion] ),
   FILTER( 'RLS', 'RLS'[Email] = CurrentUser )
  )

 // Build the allowed conditions for dimension matches.
 // If an allowed value is provided then only matching rows will be visible.
 // Also, if an exclusion value exists, rows with that value get filtered out.
 VAR OrgUnitCondition =
  ( ISBLANK( AllowedOrgUnit ) || 'Data'[Org Unit] = AllowedOrgUnit )
   && ( ISBLANK( ExcludedOrgUnit ) || 'Data'[Org Unit] <> ExcludedOrgUnit )

 VAR TeamCondition =
  ( ISBLANK( AllowedTeam ) || 'Data'[Team] = AllowedTeam )
   && ( ISBLANK( ExcludedTeam ) || 'Data'[Team] <> ExcludedTeam )

 VAR DepartmentCondition =
  ( ISBLANK( AllowedDepartment ) || 'Data'[Department] = AllowedDepartment )
   && ( ISBLANK( ExcludedDepartment ) || 'Data'[Department] <> ExcludedDepartment )

 VAR FunctionAndRoleExclusion =
  ( ISBLANK( ExcludedFunction ) || 'Data'[Function] <> ExcludedFunction )
   && ( ISBLANK( ExcludedRole ) || 'Data'[Role] <> ExcludedRole )

 RETURN
  // A row is visible if either the user is a “view all” user,
  // OR if all the matching conditions are met,
  // AND the Data row does not belong to the current user.
  ( ViewAllFlag = "Y" ||
   ( OrgUnitCondition &&
    TeamCondition &&
    DepartmentCondition &&
    FunctionAndRoleExclusion )
  )
  && ('Data'[Employee ID] <> RLS_EmployeeID)

    Best Regards

    • IzBell's avatar
      IzBell
      Frequent Visitor
      1 year ago

      Hi Anonymous 

      Thank you very much for your reply.

      The formula is very close, but two users are not getting the expected results.


      For [email protected]

      • it picks up 3 records rather than 2.

      • Expected result - it should not include Singapore.

       

      For [email protected]

      • it picks up 2 records rather than 5.

      • Expected result - it should only exclude record where DC + Result + HR + HR Manager.


      Kind regards,

      IzBell

      • v-aatheeque's avatar
        v-aatheeque
        Community Support
        1 year ago

        Hi IzBell ,

        For the above scenario for  [email protected]

        • This was happening because your email has multiple access entries in the RLS table (e.g., one for Canada and one for USA). However, the previous rule was only picking one row instead of combining them.

        [email protected] seeing 2 records instead of 5

        • This user had several exclusion conditions like Team, Org Unit, Role, etc. Earlier, these were getting mixed together, which caused valid rows to be hidden.
        • Ensure that each exclusion rule is treated separately data is only excluded if it matches all conditions from any one exclusion row, which is the correct behavior.

        Hope this helps !

        If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

        If you continue to face issues, feel free to reach out to us for further assistance!