Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
pgarman
New Member

Linking multiple tables to show a hierarchy in table

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?

 

 

3 REPLIES 3
pgarman
New Member

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:

 

Bus Req Table.png

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

 

Stakeholder Req Table.png

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

 

Functional Req Table.png

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

 

Relationship Table.png

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:

 

Desired Output.png

 

 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:

 

Screenshot 2024-02-27 at 3.09.08 PM.png

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
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.