role level access and

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Power BI training and help » Role level access and automating access based on windows log in

Role level access and automating access based on windows log in

resolvedResolved · Urgent Priority · Version 365

Role level access and automating access based on windows log in

Hello,

I have a dashboard that I am looking to publish to the management within the business. The issue/question I have is to do with logins and access to the data in the dashboard.

My expectation is that when a manager logs in they would only see their team's data presented. So for example in the Finance team:

Finance Director (has 2 senior managers reporting to them) - logs in to see all department data
Senior Finance Manager 1 - logs in to see their teams data
Manager 1 - see their team data

Senior Manager 2 does not see team data for Senior Manager 1.

Hope all that makes sense. Its almost like a hierarchy.

The question I have relates to role level access this require manual intervention and mapping departments to people. Is there a way to do it so it picks it up based on person logging in so no need for manual intervention?

Whats the alternative solution if this is not possible to avoid having to almost build a hierarchy within the role level access?

RE: Role level access and automating access based on windows log

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


RE: Role level access and automating access based on windows log

Thanks so much, Martin this is really helpful.

Does this mean that on entry of the dashboard users will be prompted to enter their email address each time to see their teams?

Ideally my preference would be that it already knows based on the MS office Share Point thats been logged in?

RE: Role level access and automating access based on windows log

Hi Chetna,

I'm glad to hear it was helpful.

In answer to your follow up question, the email address is what is used to map the users' access to their team's reports through the Role Level Security settings set up before a report is shared to the user. So there is no need for any email prompt, it just gets viewed once the report is shared.

I hope this answers your question

Kind regards
Martin

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

 

Training courses

 

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Power BI tip:

Master the Power BI Custom Visuals

Power BI offers a variety of custom visuals created by the community and Microsoft. Explore these visuals to enhance your reports and dashboards. Whether it's a custom map, a timeline, or a unique chart type, custom visuals can add significant value to your visualizations.

View all Power BI hints and tips


Server loaded in 0.09 secs.