Role level access and automating access based on windows log in

Hi Chetna,

Thankyou for your enquiry about Role Level Security Hierarchy. This involves a number of steps to achieve this hierarchy

- Create an Email address table with a unique list of Staff ID's and their emails (in Excel)
- Create another Excel table with Staff ID list, Manager ID they report to and user names (Staff ID table)
- Create a 3rd Excel table that contains transactional data with each staff making many transactions (again Staff ID listed)
- Get data to connect to these tables
- Create a table visual showing some metrics eg. Staff performance
- Create a DAX column in the Staff ID table to show the Hierarchy path:


Hierarchy Path = PATH(StaffID table[Staff ID],
StaffID table[Reports to])


The Hierarchy Path shows who reports to who starting from the right eg. Assistant Manager 1 (1004) reports to Manager 1 (1002) who in turn reports to Senior Finance Manager 1 (1001)
So the Hierarchy Path is 1001|1002|1004

- Create User ID as a measure:


UserID = USERPRINCIPALNAME()


This allows any user to enter their AD email as a variable field when setting RLS

Whatever the email address entered, this needs to be mapped to the Staff ID. Create a DAX measure:


Current UserID = LOOKUPVALUE(StaffID table[Staff ID],
EmailTbl[Email],
[UserID])


- Create a Role based on what a manager can see below them
- Go to the Modelling tab > Manage Roles > Create - type in
Hierarchy as a name
- Select the Staff ID table with the Hierarchy Path:
- In the DAX window type:

PATHCONTAINS ([Hierarchy Path],[Current UserID]) = True

This will state as True all Current UserID's appearing in the Hierarchy Path

- Now click on 'View as', tick both Hierarchy and Other User boxes and enter a specific email address to check what view they will see once the report is published, the role is assigned and the report is shared.


I hope this is what you are looking for. Please let us know if it has worked

Kind regards
Martin
MOS Applications Trainer