Row Level Security based on USERPRINCIPALNAME() and Company hierarchy

[_Martin123]

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: User_Europe@company.com 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.

[_Martin123]

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? 

[v-xinruzhu-msft]

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.

[v-xinruzhu-msft · Accepted solution]

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.