cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kcdask Frequent Visitor
Frequent Visitor

Joining multiple fields between two tables

Hi there,

 

I have been attempting to solve this issue for awhile but have hit a snag. I'd like some assistance to create a report to populate the points for fields Drive 1, Driver 2, Driver 3, RGMP, Captain and Turbo in Table 1 for the corresponding Grand Prix in Table 2.

 

For example, the report would have a chart showing:

  • Australia
  • Phil
  • Driver 1: Daniil Kvyat 48 points (points from Table 2)
  • Driver 2: Kimi Raikkonen 42 points
  • Driver 3: Romain Grosjean -6 points
  • Captain: Daniil Kvyat 48 points

Table 1

PlayerEntry.JPG

 

 

 

 

Table 2

 

Driver.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I understand in Power BI only one field can be joined between two tables. In this case, how to join multiple fields between two tables?

 

Thanks,

 

Philip

3 REPLIES 3
Super User IV
Super User IV

Re: Joining multiple fields between two tables

Hello @kcdask 

In PowerQuery, you could unpivot these columns before loading it into table 1

unpivot.jpg

Then in both Table 1 and Table 2 create a column that combintes [Grand Prix] and [Driver] then join the two tables on that field

kcdask Frequent Visitor
Frequent Visitor

Re: Joining multiple fields between two tables

I'd like to create a relationship between the two tables so that points in Table 2 would populate for the corresponding driver in Table 1 for the specific Grand Prix. 

 

Also, I would like to create a hierarchy in Table 1 in the order of Player>Drivers 1, 2, 3 for points scored. Creating a relationship between the two tables is best to achieve this outcome?  

Super User IV
Super User IV

Re: Joining multiple fields between two tables

Yes, creating the relationship is the way to go.  Do you have an .pbix file you with the data that you can share?

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors