Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
_Martin123
Frequent Visitor

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: 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_0-1681466191362.png

 

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1681787507955.png

 

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.

 

View solution in original post

3 REPLIES 3
_Martin123
Frequent Visitor

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? 

Hi @_Martin123 

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

vxinruzhumsft_0-1681810507901.png

 

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

vxinruzhumsft_1-1681810584672.png

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
Community Support
Community Support

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

vxinruzhumsft_0-1681787507955.png

 

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.