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 ...
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.

     

_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.