Lookupvalue only returns first match

[nicolasvc]

I have two tables, in the first I have a product for the first week of the month (the week is the number in the year), and in the second table I have the value of each product but for all the weeks of the year, and I need in the first Table capture the value of the product of the second in the specific week, for that I used the lookupvalue function but for some reason it only returns the first match and then pure blank, since it has the same format in the columns. What can be?

Table A

Store	Product	year	month	week
1	A	2021	1	1
1	B	2021	1	1
1	A	2021	2	5
1	B	2021	2	5
...	...	...	...	...

Table B

Store	Product	year	month	week	value
1	A	2021	1	1	2000
	A	2021	1	2	300
...	...	...	...	...	...
1	A	2021	2	5	850
...	...	...	...	...	...

valueTableA = lookupvalue(tableB[value], 

tableB[Store],tableA[Store],
tableB[year],tableA[year],
tableB[month],tableA[month],
tableB[week],tableA[week])

And it returns only 2000.

[Greg_Deckler]

@nicolasvc LOOKUPVALUE in my opinion is not reliable. Try using MAXX(FILTER(...),...) instead.

[nicolasvc]

@Greg_DecklerThanks for the answer, but I think I need a little help, I don't know and I couldn't how to adapt the lookupvalue query using MAX and FILTER 😞

[Greg_Deckler]

@nicolasvc Try:

valueTableA = 
  VAR __Store = tableA[Store]
  VAR __Year = tableA[year]
  VAR __Month = tableA[month]
  VAR __Week = tableA[week]
RETURN
  MAXX(FILTER(tableB, tableB[Store]=__Store && tableB[year]=__Year && tableB[month]=__Month && 
tableB[week]=__Week),tableB[value])

[goncalogeraldes]

Hello there @nicolasvc ! This looks like something I would either do with the RELATED() function or through a merge in Power Query. Why not try it this way?