How to join three tables without primary key?

Question

I am currently working in power bi but this has failed in other platforms also. I am getting invalid joins/ambiguity errors with the following database design. I cannot modify the database.

I have the following data source in excel -

BooksIssued -

Date        EmployeeID    Book Number
01-02-2021  1             45
02-05-2021  1             56
05-07-2021  2             78

AccessCodes -

EmployeeID  AccessCode
1           A
2           B

BookInventoryDetails -

AccessCode Book Number BookQty  Days
A          45          2        7
A          56          3        3
A          78          1        999
B          45          123      10
B          56          555      30
B          78          666      40

I want to join the tables such that my output should be -

EmployeeID Book AcessCode BookQty Days
1          45   A         2       7
1          56   A         3       3
2          78   B         666     40

Output Description - I want to see for every employee, the book he has issued, his access code, and the quantity and days associated with that access code for that particular book.

The output that I am getting -

EmployeeID Book AccessCode BookQty Days
1          45   A          2       7
1          45   A          123     10
1          56   A          3       3
1          56   A          555     30
2          78   B          1       999
2          78   B          666     40

I can't understand why I am getting ambiguous results.

Answer 1

You need to create these following relationships :

Then you create a calculated table :

ResultTable = 
SELECTCOLUMNS(
    'BooksIssued',
    "EmployeeID", 'BooksIssued'[EmployeeID],
    "Book", 'BooksIssued'[Book Number],
    "AccessCode", RELATED(AccessCodes[AccessCode]),
    "BookQty", LOOKUPVALUE(BookInventoryDetails[BookQty], BookInventoryDetails[AccessCode], RELATED(AccessCodes[AccessCode]), BookInventoryDetails[Book Number], 'BooksIssued'[Book Number]),
    "Days", LOOKUPVALUE(BookInventoryDetails[Days], BookInventoryDetails[AccessCode], RELATED(AccessCodes[AccessCode]), BookInventoryDetails[Book Number], 'BooksIssued'[Book Number])
)