Forum Discussion
Helper IIILookupvalue only returns first match
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.
4 Replies
- goncalogeraldes
Super User
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?
- Greg_Deckler
Community Champion
nicolasvc LOOKUPVALUE in my opinion is not reliable. Try using MAXX(FILTER(...),...) instead.
nicolasvcGreg_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])
