Not able to select unique rows

[Anonymous_Naq]

Here is my data in excel :
IssuedBooks :

date	EmployeeID	Book
01-02-21​	1​	45​
02-05-21​	1​	56​
05-07-21​	2​	78​

BookInventryDetail

BookCode	Book	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​

AccessLevel-

EmployeeID	BookCode
1​	A
2​	A

This is the way my tables are linked in power bi



I am creating a simple test table where the output looks like this -



But The correct output is

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

Why is it not choosing BookQty, Days corresponding to the BookCode? Why is it choosing extra records? Why the join is not working?

 

[Chihiro]

You have multiple path from AccessLevel to BookInventryDetail. That is causing ambiguity in data.

How you should model data, will depend on few things...
But read through link to understand importance of appropriate data model in BI (PowerBI, Excel Data model) etc.

Understand star schema and the importance for Power BI - Power BI

Understand a star schema and its relevance to developing Power BI data models optimized for performance and usability.

learn.microsoft.com

Your model and data doesn't have enough info for me to help you model things. As I am not sure what your end goal is.

 

[Anonymous_Naq]

Thank you for the link. I wanted to display the employee, each book that he has issued(Both these columns are in the IssuedBook sheet), the code associated to him(This info is in the access level sheet), and the BookQty and Days associated to that code(These two are in the Book inventory details sheet).

I had thought that doing issedbook.employeeid = accesslevel.employeeid, and accesslevel.bookcode = bookInventoryDetail.bookcode
And
issuedbook.book = bookInventoryDetail.book

would work because we would first select all the employee ids and then based on the id the associated code, and then based on the code and the booknumber, we will uniquely be able to select the bookqty and days. I thought i was using a combination of booknumber and bookcode in the bookInventory table to select bookQty and Days.

 

[Chihiro]

Hmm, best bet in your case is to just merge Access level table to IssuedBooks.
Since BookInventryDetail has no employee info.
You'd connect the two using "Book".