Creating a Hierarchy Matrix from Related Tables

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