Security Roles and permissions hierarchy query

Power BI
1

Hi all,

I am trying to implement a security hierarchy for my Power BI report, however I’m unsure of the format that I need.

I have attached a replica where i have recreated a sample of my data.

  1. Finance data - this will be coming from my general ledger table, and contain various transactions. There will always be a department code associated to the transaction which will be used to ensure only the relevant users have access to what they should see.
    image

  2. Org/Hierarchy - this table defines the levels that a department code sites under.
    The top level is the ‘Group’ e.g. UK, US
    The next level down is the Business Unit, e.g. business unit 1, 2, 3.
    In my real dataset, there are 4 levels in the hierarchy rather than two.
    image

  3. Security roles - this table defines which user email address has access to which level of data.
    e.g. Chris has access to the UK group, meaning he should see all of the the REGA and REGB data as they both fall under the UK.
    David has access to Unit3, meaning he can see REGC and REGD which both fall under the US group.
    image

My question is, how to I set up my data model and RLS to ensure that the various access levels in the hierarchy can be adhered to?
I think there is a table missing in my data model, or something needs to be flipped around to make this work as I am not able to create a 1:N relationship between the Security Roles and hierarchy table. Or perhaps I need to do this in DAX?

Any help would be much appreciated! Thank you
Permissions RLS Sample.pbix (34.3 KB)

2

Hi @Clolawra ,

To implement a security hierarchy for your Power BI report using Row-Level Security (RLS), follow these steps:

  1. Create Relationships:
  • Establish a relationship between the Finance Data table (containing transactions and department codes) and the Org/Hierarchy table based on the Department Code.
  • Create a relationship between the Org/Hierarchy table and the Security Roles table based on the corresponding hierarchy levels.
  1. Set Up RLS:
  • Go to the “Modeling” tab and select “Manage Roles.”
  • Create roles based on the security hierarchy. Use DAX filters to apply security rules. For example, for the Group level:
  • Similarly, create roles for Business Unit and other levels if needed.
DAXCopy code[Group] = LOOKUPVALUE(SecurityRoles[Group], SecurityRoles[User], USERPRINCIPALNAME())
  1. Testing:
  • Test the roles by using the “View As” feature to ensure the correct data visibility for different users.
  1. Deploy:
  • Publish the Power BI report and assign the roles to the respective users in the Power BI Service.

Refer to Data Mentor for further details on implementing complex RLS scenarios: Data Mentor Queries.

If you need further assistance, feel free to post your query on the Data Mentor site for a more tailored solution.

Cheers,

Enterprise DNA Support Team

3

Apologies for the belated response to this but thanks very much for getting back to me! The lookup to the security roles worked a treat :smile: !

Thank you!