Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  RLS with Dynamic Dual Hierarchy

    Posted Mar 15, 2021 05:10 PM

    Really hoping for some out-of-the-box help!

    TL;DR: Need RLS help with dual hierarchies. Admin should be able to see everyone in their department.

    I'm working on an "Accountability" dashboard for our organization. A contractor has developed a PowerApp to help track 'accountability' - basically a way for employees to check-in and take attendance daily. Employees can self-report or their admin officers can check them in.

    Primary tables are the ID table - one row per employee; unique identifiers include an ID # and email address. It includes columns that flag the user as an accountability officer or admin. It also includes columns for the department or office the employee works in. The accountability table records the employee's status for the day. Linked with the ID #, it includes their status (present, telework, vacation, etc) and timestamp.
    There are two levels of hierarchy at play. One is the employee's role. From lowest to highest, you're either Staff > Accountability Officer > Admin > Master. There's a four-row table that identifies the parent/child roles and relationship for these roles. The Roles column is linked to the Roles in the IDs table. The second level of the hierarchy is at the department/organization level. From lowest to highest, there's Office > Department > Organization. These are columns within the ID table and set as a hierarchy there.

    I've read so many articles and watched so many videos, but can't seem to get the RLS to work in this situation. I'll add a few screenshots, but don't have a dataset or .pbix I can upload at the moment due to PII. I'll see if I can recreate a dummy file.

    • Staff - should only be able to see their own record and no one else (and slightly irrelevant, since most staff won't have access to where the report gets published).
    • Accountability officers - should be able to see the status of everyone in their same department.
    • Admin - very similar to accountability officers; should see everyone in their department or maybe their organization (next level above department).
    • Master - extremely limited, by name permission who see everyone and everything.

    I've created measures:

    User = USERNAME()
    Userprincipal name = USERPRINCIPALNAME()

    I've only gotten as far as adding [email_addr] = USERPRINCIPALNAME() when creating roles, which allows the logged-in user to see their own information. I appreciate any insight, tips, or suggestions anyone can provide. Thank you!!

    Kellie Delmonico
    Statistical Assistant

  • 2.  RE: RLS with Dynamic Dual Hierarchy

    Posted May 17, 2022 10:30 AM

    Hi kellie,

    Did u get the solution for this issue , even i am stuck at the same scenario.

    sneha sai GMK