How can I create two one-to-many relationships between two tables with the same Primary Key in Power BI?

Question

I have two tables; one called 'Application' the other called 'Connection'. The Application has a column [ID] and Connection as two columns [Application_ID1] and [Application_ID2]. The 'Application' table has other columns called [Status], [Theme], [Type], [Class], [Stage], and [Phase].

Therefore: Application.ID = Connection.Application_ID1 AND Application.ID = Connection.Application_ID2.

I would like to create some visuals that allows the user to filter between the other columns ([Status], [Theme], etc...) and return a table visual that shows all "Connections" (connections that contain [Application_ID1] and/or [Application_ID2]) that fall under those what was filtered.

Example: If I filter [Theme] to "Foundation", the Application table will return 5 applications (SQL, Power BI, PowerApps, PowerPoint, Excel), the table visual of the 'Connections' table should return the following:

Application_1	Application_2
SQL	MS Teams
MS Word	Excel
Power BI	PowerApps

The only problem is Power BI does not allow two relationships between two tables.

1.) I tried duplicating the Connection table and putting a UNION on them, making various measures to at times count one of the Connection tables vs. both. I run into an issue when I filter an Application and want to show when that application is [Application_ID1] and [Application_ID2].

2.) I tried duplicating the Application table, but was unsure how I can create a measure when filtering by Application to include both tables.

Answer 1

One of those relationships will have to be Inactive. Only one Active relationship is allowed between two tables, but you can have multiple Inactive ones.

Then for measures, you can use USERELATIONSHIP function to "activate" an Inactive measure. For example:

Count Conn 1 = COUNTROWS('Connection')

Count Conn 2 = CALCULATE(
  COUNTROWS('Connection'),
  USERELATIONSHIP('Application'[ID], 'Connection'[Application_ID2])
)

You may find this a useful read: Active vs inactive relationship guidance

For example, if you wanted to show rows in Connection based on the selection/filtering on Application, you could create a measure like this one.

Connection Count = 
  var x1 = COUNTROWS('Connection')
  var x2 = 
    CALCULATE(
      COUNTROWS('Connection'),
      USERELATIONSHIP('Application'[ID], 'Connection'[Application_ID2])
    )
  return x1 + x2

Then add this measure to your Connection table visual.