Linking multiple tables to show a hierarchy in table

[pgarman]

Hello,

I have several tables in Power BI desktop pulling data from a REST API.

Table 1 fields/columns:

• Unique ID
• Name
• Description
• Status
• Priority

Table 2 fields/columns:

• Unique ID
• Name
• Description
• Status 
• Priority

Table 3 fields/columns:

• Unique ID
• Name
• Description
• Status
• Priority

Table 4 fields/columns:

• Upstream Item Unique ID
• Downstream Item Unique ID

Items from Table 1 will be upstream from items in Table 2, and items in Table 2 will be upstream from Table 3.  Table 4 has rows for each related item 'pairing' and the match is made based on the unique IDs - for example:

Row in Table 1 has unique ID = ABC, related row in Table 2 has unique ID = DEF, and row in Table 3 related to row in Table 2 has unique ID = GHI.

Table 4 would have rows:

Upstream Unique ID                  Downstream Unique ID

ABC                                            DEF

DEF                                            GHI

I have table relationships set up in Power BI Desktop so that:

1. Table 1 Unique ID -> Table 4 Upstream Unique ID
2. Table 2 Unique ID -> Table 4 Upstream Unique ID
3. Table 2 Unique ID -> Table 4 Downstream Unique ID
4. Table 3 Unique ID -> Table 4 Downstream Unique ID

Each Unique ID from Tables 1, 2, and 3 might appear in either column of Table 4 multiple times.

On the dashboard, I would like to create a table widget with headers and content like this:

Table 1 Name  ->  Table 2 Name -> Table 2 Description -> Table 2 Priority -> Table 3 Name -> Table 3 Status

<Name for row ABC>       <Name for row DEF>          <Description for row DEF>     <Priority for row DEF>    <Name for row GHI>     <Status for row GHI> (all in a single row of the dashboard table)

What I'm getting instead is:

Row 1 of dashboard table:  <Name for row ABC>  <Name for row DEF> <Description for row DEF> <Priority for Row DEF>

Row 2 of dashboard table:  <Name for row DEF> <Description for row DEF> <Priority for Row DEF> <Name for row GHI> <Status for row GHI>

What am I doing wrong that is splitting the data from the three table items into separate rows for each related pair?

[pgarman]

Hello,

Adding some screenshots to hopefully provide some clarity.

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?

[v-shex-msft]

Hi @pgarman ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[pgarman]

I added a comment with screenshots from Power BI desktop including hte error message I am getting when trying to make both table links active at the same time.