Forum Discussion

_Martin123's avatar
_Martin123
Frequent Visitor
3 years ago
Solved

Row Level Security based on USERPRINCIPALNAME() and Company hierarchy

Hi Community, I need your help 🎉

The scope of this task is to grant users row level access based on their USERPRINCIPALNAME(), and the company hierarchy they belong to. There are approximately 4.500 employees that require access to this report, and they are only allowed to see the company hierarchy that they belong to, based on a parent, child relationship.  There are +120 company entities.

EXAMPLE

PBI example file  - https://drive.google.com/file/d/10JJ77ZgZOYJuM1bAnlDT_ga-30g1d8qh/view?usp=sharing

If user mail: [email protected] accesses the dashboard, then row level security should be setup to restrict entity sales only related to European territories i.e., Europe, Nordic, Denmark, Jutland), based on table: entity hierarchy.

My hope is to find a solution where row level security is based on 2 tables: 1) User Entity, and 2) Entity Hierarchy, as these two tables can be extracted directly from Azure Active Directory and require little space in Power BI.

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi _Martin123 

    I delete the relationship among the three tables, then create dax in RLS

    You can refer to it.

     

    VAR _userentity =
    CALCULATE ( MAX ( RLSUser[Entity] ), RLSUser[User mail] = USERPRINCIPALNAME () )
VAR _entityID =
    CALCULATE ( MAX ( RLSEntity[Entity ID] ), RLSEntity[Entity] = _userentity )
VAR _filter =
    SUMMARIZE (
        FILTER ( RLSEntity, CONTAINSSTRING ( [Path], _entityID ) ),
        [Entity]
    )
RETURN
    [Entity] IN _filter

     

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi _Martin123 

    I delete the relationship among the three tables, then create dax in RLS

    You can refer to it.

     

    VAR _userentity =
    CALCULATE ( MAX ( RLSUser[Entity] ), RLSUser[User mail] = USERPRINCIPALNAME () )
VAR _entityID =
    CALCULATE ( MAX ( RLSEntity[Entity ID] ), RLSEntity[Entity] = _userentity )
VAR _filter =
    SUMMARIZE (
        FILTER ( RLSEntity, CONTAINSSTRING ( [Path], _entityID ) ),
        [Entity]
    )
RETURN
    [Entity] IN _filter

     

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • _Martin123's avatar
    _Martin123
    Frequent Visitor
    3 years ago

    Hi Yolo, 

    Thank you so much. Your solution worked 🎉

    I my example, I only added one fact table: Entity Sales, but I recognize that several fact tables are needed when building reports from several data sources. If I follow your methodology, then I just need to ad your DAX expression to each added fact table, and ensure that [Entity] is included for filtering. 

    Would it make sense to base the RLS dax expression on table: Entity Hierarchy (table name in shared Power BI: RLSEntity) instead, so we only add RLS to a single dimension table, rather than adding it to every fact table that we include?

    It could be that +7 fact tables are required, and I am wondering if your recommended setup will slow performance, or what is your experience? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi _Martin123 

      If you have multiple fact tables, perhaps you might consider concretizing the hierarchy in ENTITY HIERARCHY , for example

       

      Then create relationship among tables and select both filter between ENTITY HIERARCHY and fact table, check the 'Apply Security.....'

      Then you only need to write[User mail] =USERPRINCIPALNAME() in RLS

       

      Best Regards!

      Yolo Zhu

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.