How to show organisation's Hierarchy in matrix

[pra137]

Hello guys,

I want to show organizational Hierarchy in matrix visual 

Eg

IF MANAGER login to report he can see his own data as well as his sales persons data under his Hierarchy..plz help

Below is my sample data 

Manager   sales person 

A                   U

P                    T  

R                    A

A                    p

A                    R

P                     S

Result should be

A

      A

      U

      P

      R

P

      P

      T

      S

R

      R

      A

Please help me how to do this in power bi

Thanks

Pra137

[v-rzhou-msft · Accepted solution]

Hi @pra137 ,

I think there may be something wrong in your sample. "User A" is the manager of "User R", however "User R" is the manager of "User A" as well. We usually use Path function to create a calculated column to show hierachy Path for organization hierachy RLS. If you use this kind of hierachy, we will get Path like A|R|A.  This hierachy is incorrect. Sales man couldn't be a manager to his manager.

Here I remove A from R. User Table should look like as below.

Sales Table:

Create a relationship: between User[ID] and Sales[User ID].

Then create a new role in User Table in Manage Role.

PATHCONTAINS(User[Path],
MaxX(
Filter(
User,
[Email]=USERPRINCIPALNAME()
)
,User[ID]
)
)

Result is as below. When you view as A, you can see all data. A is the biggest manager.

When you view as P, you can see data for P, T and S.

For more details, you may refer to this blog: Dynamic Row Level Security with Organizational Hierarchy Power BI

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@pra137 check this for RLS and hierarchy What Do You Need to Implement Dynamic Row-Level Security in Power BI? - RADACAD

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡