Forum Discussion

Saxon202202's avatar
Saxon202202
Icon for Helper III rankHelper III
2 years ago
Solved

Lookupvalue Return Max Value

I have two tables named "Data" and "Report." The Data table comprises columns such as Area Code, Type, Rack Length, Rack Width, Rack Height, and Volume, which is determined based on the rack dimensi...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    Try this calculated column formula

    Volume = CALCULATE(MAX(DATA[Volume]),FILTER(DATA,(DATA[Area Code]=EARLIER(REPORT[Area Code])&&DATA[Width]>=EARLIER(REPORT[Pack Width])&&DATA[Height]>=EARLIER(REPORT[Pack Height])&&DATA[Depth]>=EARLIER(REPORT[Pack Length]))||(DATA[Area Code]=EARLIER(REPORT[Area Code])&&DATA[Width]>=EARLIER(REPORT[Pack Length])&&DATA[Height]>=EARLIER(REPORT[Pack Height])&&DATA[Depth]>=EARLIER(REPORT[Pack Width]))))

    Hope this helps.

     

vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
2 years ago

its great that you  have provided the data, but can you please rather provide the logic / business rule for the columns rather than providing the excel formula which is difficult to read. 

 

also in the report table, you have a column called type where does that column come from?

  • Saxon202202's avatar
    Saxon202202
    Icon for Helper III rankHelper III
    2 years ago

    vanessafvg, thank you for your reply.

     

    Basically the small pack data need to be found smaller rack rather than going to bigger rack. 

    • vanessafvg's avatar
      vanessafvg
      Icon for Community Champion rankCommunity Champion
      2 years ago

      based on what combination, need confirmation, do you only use area code?  if you use item and type, how do those link between the tables?