cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

building a hierarchy in the matrix table

I am trying to create a matrix table where I could show my organizational chart through a hierarchy based on who is reporting to whom based on the designation. For Example : Resource -> Supervisor -> Manager -> Director -> Sr Director -> Vice President -> SR Vice President OR Resource -> Manger -> Director -> SR Vice President. The issue is that I need to create a hierarchy based on the organizational hierarchy the psoition is designated but since the data is segregated into different levels based on the first level of data, the dataset looks like below

 

Image1.png

 

 

And I need a table showing an hierarchy something like this

Image2.png

 

Ideally I would have segregated the dataset based on the different designation to create a better hierarchy but since different people have different designation in the same row I am unable to create a hierarchy. 

 

I created a query to sort out the resoures to a unique column based on the levels in a particular row are empty or not.

 

ResourceName_v2 = IF(OrgHierarchy_v2[ResourceName_2]="", (IF(OrgHierarchy_v2[ResourceName_1]="",(IF(OrgHierarchy_v2[Supervisor]="",(IF(OrgHierarchy_v2[Manager]="",(IF(OrgHierarchy_v2[Director]="",(IF(OrgHierarchy_v2[SR Director]="","Empty",OrgHierarchy_v2[SR Director])),OrgHierarchy_v2[Director])),OrgHierarchy_v2[Manager])),OrgHierarchy_v2[Supervisor])),OrgHierarchy_v2[ResourceName_1])),OrgHierarchy_v2[ResourceName_2]) 

 

 

Is there a way or a visual where I can get the correct hierarchy to display the relavent information? Any help would be appreciated.

 

Sample data is uploaded to the following onedrive location

https://1drv.ms/x/s!AkUUe-6V4o1Ug2RxVsHLIVzU8vtV

 

Thanks

Sridhar

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

@Anonymous,

 

Follow the below steps,

 

Matrix.gif

 

Let me know if anything is required.


Best Regards,
Siva Mani

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

hi @v-xjiin-msft,

I need your help, as when i am trying to create hierarchy using matrix visual.

Facing below issues:

 

1) Blanks are appearing, if trying to apply visual filter complete level from its root gets vanished.

Please provide a way to avoid blank in hiearrachy as it happens in hierarchy slicer.

 

2) there are 4 more slicers apart from matrix slicer as and when i select any other slicer, matrix slicer automatically shows result of its top most member..it is quite annoying, have i need to set an property to stop defaulting to top most member even if it is selected somewhere else.

 

Your help is needed.Thank you in advance.

v-xjiin-msft
Solution Sage
Solution Sage

Hi @Anonymous,

 

Sorry but I'm not quite understand your requirement. And the column names in your shared sample data don't match the column names in your query. So I don't understand your query either.

 

Based on your sample data, did you mean the first level of hierarchy is ResourceName as in sheet OrganizationHierarchy, it is the values from right side? 

 

Could you please share is your desired result based on your sample data? So that we can get a right direction.

 

Thanks,
Xi Jin.

SivaMani
Super User
Super User

Hello @Anonymous,

 

Is that your expected result(based on the sample data)?

 

Matrix.PNG


Best Regards,
Siva Mani
Anonymous
Not applicable

@SivaMani Yes, this is the excepted result. Can you share the sample .pbix file or let me know how to achieve the excepted result?

@Anonymous,

 

Follow the below steps,

 

Matrix.gif

 

Let me know if anything is required.


Best Regards,
Siva Mani

View solution in original post

@SivaMani- Kudos to you for including a video tutorial in your answer.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!