Forum Discussion

manoj_0911's avatar
manoj_0911
Icon for Kudo Commander rankKudo Commander
2 months ago
Solved

Dynamic RLS for Division-Based Access Using USERPRINCIPALNAME() and Multiple Division Memberships

Hi Community,

I am working on implementing Dynamic Row Level Security (RLS) in Power BI for an Agent Performance report.

Current Model:

Fact Table:

  • AG2_AGENT_SUBHOUR_V

Dimensions:

  • PERSONS_V

  • DIVISIONS_V

Relationships:

  • AG2_AGENT_SUBHOUR_V[USER_ID] -> PERSONS_V[USER_ID]

  • PERSONS_V[DIVISION_ID] -> DIVISIONS_V[DIVISION_ID]

Requirement:
A logged-in user should be able to see all agents belonging to the same division(s).

Example:

User:
[email protected]

PERSONS_V contains:

EMAIL_ID DIVISION_ID
[email protected] 7fe04c3e-b40a-448a-9f74-8a7bef8110f9
[email protected] 5e815414-4206-4efa-8ab3-873fa39e01ba

Both DIVISION_ID values map to the same DIVISION_NAME:

Claims

What has been tested:

  1. User-level RLS

PERSONS_V[EMAIL_ID] = USERPRINCIPALNAME()

Result:
Successfully restricts data to the logged-in user.

  1. Division-level RLS

Created a security table:

RLS_DIVISION =
DISTINCT(
SELECTCOLUMNS(
PERSONS_V,
"EMAIL_ID", PERSONS_V[EMAIL_ID],
"DIVISION_ID", PERSONS_V[DIVISION_ID]
)
)

Relationship:
RLS_DIVISION[DIVISION_ID] -> DIVISIONS_V[DIVISION_ID]

Role Filter:

RLS_DIVISION[EMAIL_ID] = USERPRINCIPALNAME()

Questions:

  1. Is this the recommended approach for division-based Dynamic RLS?

  2. Is there a better model design to support users belonging to multiple divisions?

  3. Should a dedicated bridge/security table be used, or should security be applied directly on PERSONS_V?

  4. Are there any concerns with enabling "Apply security filter in both directions" for this scenario?

Any recommendations or best practices would be greatly appreciated.

Thank you.

Need Help
  • v-abhinavmu's avatar
    v-abhinavmu
    2 months ago

    Hi manoj_0911,

    Thanks for reaching out to the Microsoft Fabric Community forum.

     

    Just to confirm, did you check both options in the relationship settings the Cross filter direction and Apply security filter in both directions? Could you also let us know which one is disabled in your model? This will help us better understand the behavior you're seeing.

     

    I hope this helps. Please feel free to reach out if you have any further questions.

    Thank you.

5 Replies

  • Shai_Karmani's avatar
    Shai_Karmani
    Icon for Super User rankSuper User
    2 months ago

    You do not actually need a separate security table here, because PERSONS_V already contains the user-to-division mapping. The simpler setup is to put the role filter directly on PERSONS_V: [EMAIL_ID] = USERPRINCIPALNAME(). Then on the PERSONS_V to DIVISIONS_V relationship, keep cross-filter as Single, open the relationship properties and tick "Apply security filter in both directions" in the advanced section. That toggle exists for exactly this scenario, it makes RLS propagate both ways without flipping the whole relationship to bidirectional. The filter then flows from PERSONS_V (the user's rows) up to DIVISIONS_V (the user's divisions), back down to all PERSONS_V rows in those divisions, and into the fact via USER_ID.

     

    A bridge table like your RLS_DIVISION is also valid and is the recommended pattern when you want to hide PERSONS_V from RLS or separate the security model from the analytical model, but for your shape it just duplicates data.

     

    On the bi-directional concern, broad bidirectional relationships introduce filter-path ambiguity and slower queries as the model grows. "Apply security filter in both directions" avoids that, because the bidirectional behavior only kicks in for the RLS user context, not for every query.

     

    Always validate with View as > Other user using a real UPN before publishing.

     

    If this helped, a thumbs up and accepting the solution would be appreciated.

     

    Best regards,

    Shai Karmani

    • v-dineshya's avatar
      v-dineshya
      Icon for Community Support rankCommunity Support
      2 months ago

      Hi manoj_0911 ,

      Thank you for reaching out to the Microsoft Community Forum. could you please try the proposed solution shared by Shai_Karmani  ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.

       

      Regards,

      Dinesh

    • manoj_0911's avatar
      manoj_0911
      Icon for Kudo Commander rankKudo Commander
      2 months ago

      Thank you for the suggestion.

      I tested your recommended approach. However, in my model the "Apply security filter in both directions" option on the PERSONS_V -> DIVISIONS_V relationship is disabled (greyed out).

      My model contains additional relationships such as:

      • AG2_AGENT_SUBHOUR_V -> GROUP_MEMBERS_V (Many-to-Many, Both)

      • GROUP_MEMBERS_V -> PERSONS_V (Both)

      • PERSONS_V -> SUPERVISORS_V (Both)

      • TEAM_MEMBERS_V -> PERSONS_V (Both)

      Could these existing bidirectional and many-to-many relationships be the reason why Power BI disables the security filter option?

      If so, would you still recommend simplifying the model, or would you recommend using a dedicated security/bridge table for this scenario?

      Thank you.

      FROM TABLE (COLUMN)CARDINALITYCROSS FILTER DIRECTIONTO TABLE(COLUMN)
      AG2_AGENT_SUBHOUR_V(DATE_TIME_KEY)Many-to-OneBothDATE_TIME_V(DATE_TIME_KEY)
      AG2_AGENT_SUBHOUR_V(ORGANIZATION_ID)Many-to-OneSingleORGANIZATION_SETTINGS_V(ORGANIZATION_ID)
      AG2_AGENT_SUBHOUR_V(QUEUE_ID)Many-to-OneBothQUEUES_V(QUEUE_ID)
      AG2_AGENT_SUBHOUR_V(UD_DIM_1_KEY)Many-to-OneSingleUD_DIM_1_V(UD_DIM_1_KEY)
      AG2_AGENT_SUBHOUR_V(UD_DIM_DEFAULT_KEY)Many-to-OneSingleUD_DIM_DEFAULT_V(UD_DIM_DEFAULT_KEY)
      AG2_AGENT_SUBHOUR_V(USER_ID)Many-to-OneSinglePERSONS_V(USER_ID)
      AG2_AGENT_SUBHOUR_V(USER_ID)Many-to-ManyBothGROUP_MEMBERS_V(USER_ID)
      GROUP_MEMBERS_V(GROUP_ID)Many-to-OneBothGROUPS_V(GROUP_ID)
      GROUP_MEMBERS_V(USER_ID)Many-to-OneBothPERSONS_V(USER_ID)
      PERSONS_V(DIVISION_ID)Many-to-OneSingleDIVISIONS_V(DIVISION_ID)
      PERSONS_V(SUPERIOR_ID)Many-to-OneBothSUPERVISORS_V(USER_ID)
      TEAM_MEMBERS_V(TEAM_ID)Many-to-OneBothTEAMS_V(TEAM_ID)
      TEAM_MEMBERS_V(USER_ID)Many-to-OneBothPERSONS_V(USER_ID)
      • v-abhinavmu's avatar
        v-abhinavmu
        Icon for Community Support rankCommunity Support
        2 months ago

        Hi manoj_0911,

        Thanks for reaching out to the Microsoft Fabric Community forum.

         

        Just to confirm, did you check both options in the relationship settings the Cross filter direction and Apply security filter in both directions? Could you also let us know which one is disabled in your model? This will help us better understand the behavior you're seeing.

         

        I hope this helps. Please feel free to reach out if you have any further questions.

        Thank you.

  • v-abhinavmu's avatar
    v-abhinavmu
    Icon for Community Support rankCommunity Support
    2 months ago

    Hi manoj_0911,

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 


    Thank you.