Help Joining Multiple Tables to a Fact Table

[PMP90]

Hey everyone, struggling with this one a bit!
I have 4 tables of data, with each representing a level on a data hierarchy. I need these to all appear in a matrix visual so each level can be collapsed etc. In each of the 4 tables, the rows have a status (Done, In Progress etc) which I need to appear in the same column in my matrix, like below:

The 4 tables are linked by a Parent column and to get the status into one row I am trying to link each of my 4 tables to an amalgamated table, but it is creating ambiguous relationships:

Anyone any ideas how to get round this? I tried the PATH function but it was of no use

[Anonymous · Accepted solution]

Hi,@PMP90 .I am glad to help you.

According to your description, you currently have four tables with hierarchical relationships, and I noticed that your tables all have the same fields between them, you can follow your preconceived idea of merging them into a single table, making sure that the data for each level is in the same column, and adding a new column to the merged table that identifies the hierarchy to which each row of data belongs, and this way you can achieve the goal of displaying the data of the same field from multiple tables in multiple tables in the same column of the matrix.
You can also try the UNION() function to splice tables with the same structure into a single table.

2. Another way is not to create a merged table.
I noticed that you have created relationships between tables by using the same fields, you can try to extract their hierarchical fields to form a new hierarchical table, similar to a calendar table with a hierarchical structure. (hierarchy table)
Add this hierarchy table to the matrix to achieve the effect you need, i.e. to create the hierarchy

Subsequently for the results of the calculations, take the creation of a measure:

By switch ()+IsInScope()
These two functions perform a hierarchical judgment in the opposite direction at MEASURE (because the smallest hierarchy is judged before the larger one)
like this.
Judge the smallest Date in the hierarchy first, and finally judge the largest Year in the hierarchy.
determine the current hierarchy by which the corresponding hierarchical fields of the table filtered to perform the corresponding calculation logic

The advantage of this is that you don't need to modify the structure of your current model or create a new merge table to merge all the data.

You can dynamically determine the hierarchical structure and perform the corresponding calculations as you originally intended or by creating a measure.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.