How can I create a relationship between rows in one table and multiple columns in another table?

[Anonymous]

Hello,

This doesn't have to be a relationship, but I presume, a measure would solve it as well. To clarify what I'm doing, I'm adding two example tables that I came up. Finally, to be clear, my whole report is working fine, and unpivoting columns unfortunately breaks my other measures.

Table1:

Contract	Sportsperson	Nationality	Salary
Temporary	Messi	Argentina	97M
Full Time	Ronaldo	Portugal	70M
Short Term	Neymar	Brazil	76M

Table2:

Income Type	Messi	Ronaldo	Neymar
Modelling	20M	35M	10M
Construction	5M	2M	1M
Influencer	200K	500K	1M

I want to ensure, for example, if someone clicks on Argentina, it shows both incomes from the regular salary and side-jobs. 

Thanks, 

[TheoC · Accepted solution]

Hi @Anonymous 

In Power Query, duplicate Table 2 and unpivot columns so that the names of the players are in rows. You can then link Table 1 to Table 3, and recreate measures as necessary. 

Hope this helps.

Theo

[Anonymous]

Thanks @TheoC 

I was hesitant to do this, because I have to create a number of new measures, which I know, I will have issues with. In fact, one of my measures is already failing, I'm getting a blank calculation. I'm trying to calculate the total gains per year. What do you think is going wrong here?

Unpivotted Table = CALCULATE(SUMX('Table1', 'Table1'[Year] = "2021"), Table1'[Player] = "Messi" || Table1'[Player] = "Ronaldo" || Table1'[Player] = "Neymar")  

Or, do you have other recommendations for my initial question?

Many thanks in advance, 

[Anonymous]

Oops! I've managed to solve it, thanks to your support @TheoC 
As reference to someone else looking for info, I changed my measure with SUM and FILTER. Created relationships, and now it's working.