Joining multiple fields between two tables

[kcdask]

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

Table 2

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

[jdbuchanan71]

Hello @kcdask 

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

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]

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?  

[jdbuchanan71]

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