Linking multiple tables via a single mapping table

[pgarman]

In a database, I have 3 different object types that I am pulling into Power BI via a custom connecter using a REST API.  In the source database, these object types can relate to one another as many to many, and I have a 4th table in Power BI with two columns - column 1 is the API ID for the 'parent item' and column 2 is the API ID for the 'child item'. In this table, any of the 3 object types could be a parent or a child.

Here is a screen shot of object type 1 - Bus Requirements:

 

Here is a screen shot of object type 2 table - Stakeholder Requirements:

 

Here is screenshot of object type 3 table - Functional Requirements:

 

And finally here is a screenshot of the table with a row for each database object pairing:

 

In this situation - Stakeholder Req API IDs will show in the toItem column of hte relationship table when there is a parent Bus Req (whose API ID would appear in the toItems column to show the object pairing).  Stakeholder Req API IDs will also be in the fromItems column of hte relationship table when it is the parent of a Functional Requirement (whose API ID would show in the toItems column of hte relationship table to show the object pairing).

On my dashboard, I want to create a table like this:

 Essentially, this dashboard table is structured so that:

Columns 1 - 3 show selected data for Bus Reqs (object table 1).  For each Bus Req, columns 4-6 should contain data for any Stakeholder Reqs (object table 2) that are children of hte Bus Req in columns 1-3.  Finally, in columns 7-9 the table would show selected data from any Functional Requirements that are children of hte Stakeholder Requiremetns in columns 4-6.  As you can see in the screenshot of the desired dashboard table above, Functional Req 1 (BABK-FR-2) is a child of both Stakeholder Req 1 and Stakeholder Req 2, so appears twice - once for each parent stakeholder req.

What I'm running into in Power BI is that I have the API ID for the Stakeholder Reqs related to the API IDs in the "fromItem" column in the relationship table AND to the API IDs in the "toItem" column of the relationship table.  That is because the Stakeholder REquirements will be BOTH a child (toItem) for Bus Reqs and a parent (fromItem) for Functional Reqs.

Power BI will not allow me to make both of these table links active, though - I get this message when I try to make both active:

 

To avoid this, do I have to make multiple versions of hte relationship table to accomodate the different object type pairings?  E.g., Relationship table for just Bus Req -> Stakeholder Req and a second table for Stakeholder Req -> Functional Req?

Or can I update the settings for the table links so that both links can be active at the same time?

[Wilson_]

Hi pgarman,

To clarify, can stakeholder and functional requirements have no parent? In other words, must a stakeholder requirement have a business requirement and must a functional requirement have both a business requirementa nd a stakeholder requirement?

To me, it seems like the main issue is there is no proper hierarchy in the table that holds all the relationships. It would be a lot simpler to do if the answer to my above question is yes, but I would first self join the table to itself twice to create a proper hierarchical relationship table.

For example, if the original table looks like:

fromItem	toItem
1	2
2	3
2	4
1	5
1	6

I would convert it to look like:

Business Requirement	Stakeholder Requirement	Functional Requirement
1	2	3
1	2	4
1	5
1	6

This table could then be joined to each of your requirements tables, so you would have a total of four tables and three relationships. If this doesn't make sense to you or is not feasible as a solution, please share an anonymized sample pbix file so I can better explain or see why that wouldn't work. (If you're not sure how to do this, please check out the pinned thread in this forum.)

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[pgarman]

@Wilson_ thanks for this tip.

To answer your first question, the relationships are not required - so it is possible, for example, that a Stakeholder Requirement would not be connected to an upstream (or parent) business requirement.

This is also a 'many to many' data model, so here are some scenarios by 'item type':

• A Business Requirement might have 0, 1, or multiple 'downstream' related Stakeholder Requirements.
• A Stakeholder Requirement might have 0, 1, or multiple 'upstream' related Business Requirements. 
• A Stakeholder Requirement might have 0, 1, or multiple 'downstream' related Functional Requirements.
• A Functional Requirement might have 0, 1, or multople 'upstream' related Stakeholder Requirements.

In other words, the relationship model is not a linear tree - the branches might intersect or merge at different levels.