Creating a Hierarchy Matrix from Related Tables

[Condor]

Hi everyone,

I'm working on a Power BI model that has several related tables, and I'm having trouble creating a hierarchy matrix from these tables. Here is my model and its relationships:

Here's some important information about my data:

• The cp_epic and cp_capa tables contain the names of projects. Epic is a type of project that is the parent, and capa is also a type of project that is related to an epic. An epic can have one or multiple capa.
• The cp_collaborateur table contains the names of people who work on the projects, and I get their name from the "FullName" column.
• The cp_candidature table is a fact table that contains information about who worked on a project from start date until end date (this is why I expanded on custom date) and they are affected to an epic or a capa.
• Some Collaborateur are related to the capa, while others are related directly to the epic.
• Each capa has a parent related to an epic, and they are related with a column named "cp_epic" in the cp_capa table.

What I want to achieve is to create a hierarchy matrix with the following structure in the rows:

Can anyone help me to create this hierarchy matrix?

Thank you in advance for your help!

[scee07]

Hi, 
there is some work to do. What is one option is that you setup a tree table structure from your date and use DAX helper function (as described below)

a) you have set up the relationships between the tables. However, this does not give you all the parent relationships automatically by travelling through the relationship

b) you have to setup an adjaceny matrix to have for  every element (here the project member names, capa and epic) And there will be the one parent id per element (for epics there is no parent so it is empty)

Example from another problem:

What is import here is that you have the all child/parent relationships in one table. So, every entity has an ID (epic, capa , project member), preferably 1 to n (n is the number of nodes in your tree)

Then you have exaclty one parent per child (otherwise the helper functions won't work). This is the case from what I see above.

c) now use the helper functions

AccountPath = PATH ( Account[AccountKey], Account[ParentKey] )
Level1 = 
VAR LevelNumber = 1
VAR LevelKey = PATHITEM ( Account[AccountPath], LevelNumber, INTEGER )
VAR LevelName = LOOKUPVALUE ( Account[AccountName], Account[AccountKey], LevelKey )
VAR Result = LevelName
RETURN

Level2 = 
VAR LevelNumber = 2
VAR LevelKey = PATHITEM ( Account[AccountPath], LevelNumber, INTEGER )
VAR LevelName = LOOKUPVALUE ( Account[AccountName], Account[AccountKey], LevelKey )
VAR Result = LevelName
RETURN
    Result
... and for the following levels
Depth = PATHLENGTH( Account[AccountPath] )

- the PATH function gives you the full path root to the regarded node

- PATHITEM gives you the key of the item in the relevant level 

So, if you setup a table anlogue to the one above, you can put it in a matrix visual and will see the hierachies

Then, you link this table via the ID to whatever value per node. All values will be summed up on the different levels according to the tree structure. 

Best regards 

Christian