Relation problem through lookuptable

[MrMarshall]

I have two tables, and a relation between them through a lookup table.
Related with ID & UserID as:

Sales: 

UserId	Profit	Date
1	2000	2018-02-01
1	2000	2018-02-01
1	200	2018-02-02

Users: 

Id	Firstname
1	Andrew

Other

UserId	OtherValue	Date
1	5	2018-02-01
1	6	2018-02-02
1	4	2018-02-02

No relation errors. I am testing this with a simple matrix, as you can see, the OtherValue column is wrong, it should be as stated with red. 
What am I doing wrong?

[Thejeswar · Accepted solution]

Hi @MrMarshall,

This is happening because you tables are joined only using UserID when the data is present for multiple days.

To Solve this join your tables using a concatenated column which is a concatenation of UserId and Date

 Screenshots for the same are given below

concatenated column

Users TablesNew relationshipFinal Output

 Hope this helps!!!

[MrMarshall]

Hi!

I understand now. That was really helpful, Thx!

Although, it requires that I would create a Date value for each unique user. 
I cannot seem to get it to work in an automated way, say if I have 100 users. 
Any ideas?

[MrMarshall]

Nvm, I figured it out! Created a list.
List.Dates([StartDate], 100, #duration(1,0,0,0))