Forum Discussion

nicolasvc's avatar
nicolasvc
Icon for Helper III rankHelper III
4 years ago

Lookupvalue 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 o...
Show and Tell
Tips and Tricks
Tutorial Requests
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

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

  • nicolasvc's avatar
    nicolasvc
    Icon for Helper III rankHelper III
    4 years ago

    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's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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])