CODEWITHSUNDEEP
relationshippowerpivotpowerbi
Myurathan Kajendran
Myurathan Kajendran

Reputation: 677

create relationship based on two columns

I have two tables like below

Table 01

Company          Date                Size

A                01/05/2000          30

A                01/06/2000          40

B                01/05/2000          80

B                01/06/2000          90



Table 02

Company          Date               sales

A                01/05/2000          30

A                01/06/2000          40

B                01/05/2000          80

B                 01/06/2000          90

I want to create relationship between these two tables based on date and company. How to create relationship between two tables?

Thanks in advance

Upvotes: 0

Views: 973

Answers (2)

Mike Honey
Mike Honey

Reputation: 15037

For a Power Pivot / Data Model, you can only use one column in a relationship. You can concatenate columns in a new calculated column using the & operator, e.g.

= 'Table 01'[Company] & "|" & 'Table 01'[Date]

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23285

Assuming your Table 1 is column A, B, C in "Sheet1", and Table 2 the same, but in "Sheet2", you can just use a Index/Match (entered with CTRL+SHIFT+ENTER.

In your Table 1, go to D2 (the first non-header row in column D, next to the 30), and use this formula:

=Index(Sheet2!$C$2:$C$10,Match($A2&$B2,Sheet2!$A$2:$A$10&Sheet2!$B$2:$B$10,0))

I'm assuming your last row is 10, if not, just change that in all parts of the formula. This should leave you with:

Table 01

Company          Date                Size

A                01/05/2000          30                30

A                01/06/2000          40                40

B                01/05/2000          80                80

B                01/06/2000          90                90

Upvotes: 0

Related Questions