Unsure how to create a relationship

[jimmyfromus]

Hi, 

I have 2 tables. 

The first table looks like this: 

contact/company           contactid            companyid
company a                                                  7
company b                                                 8
company c                                                  9
contact a                           4  
contact b                           5  
contact c                           6  

The second table like this: 

contact           contactid         company                companyid
contact a         4                    company a                   7
contact b         5                    company b                  8
contact c         6                    company c                   9

If I create a relationship between the 2 tables via the contactid, I can click on a contact, contact a for example, in table 1 and the contact a in table 2 will also react. I would however like that when I click on a company (company a for example) in table 1, the same line with company a and contact a will react in table 2. Is there a way to do this? Thank you. 

[parry2k · Accepted solution]

@jimmyfromus solution attached, tweak it as you see fit.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@jimmyfromus the solution provided by @PaulDBrown  is the way to go for it. I created two different dimension tables from table 1 and then created a relationship with the 2nd table on ids and then it is all good.

See attached solution file, there are many tables in it, just focus on page 4 and the tables related to it

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[PaulDBrown]

@jimmyfromus 

I suggets you create 2 dimension tables directly from Table 2: one for Contacts and the other for Companies.

If you join these dimension tables with Table 2 in one-to-many relationships, you can use either or both of these dimension tables as slicers or filters to filter the rows in Table 2.

[jimmyfromus]

@PaulDBrown @parry2k Thank you but not quite. I've added my first and second table to the pbix file. Is there a way to click on a company or company in the first table so that they are highlighted in the second table? The slicers unfortunately aren't an option here. Thanks again. 

Download pbix