Lookupvalue Return Max Value

[Saxon202202]

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 dimensions.
In the Report table, there are columns like Item, Area Code, Pack Length, Pack Width, and Height, with the Area Code serving as a key relationship linking the two tables.

In the context of the Data table, my goal is to determine the suitable rack size based on the pack dimensions associated with a specific area code.
To aid in this, I created a volume helper column in the Data table using the rack dimensions.

The primary purpose of this helper column is to identify the minimum volume when multiple sizes are matched.
For instance, it is designed to pinpoint the rack with the smallest volume among three racks capable of accommodating one of the boxes out of the twelve available.

Currently, I am exploring the possibility of a DAX code to create a new calculated column that achieves the same result.

To achieve this in Excel, I employ the following formula:
=IFERROR(LOOKUP(2,1/(DATA!$F$2:$F$15=1/(1/MAX(((DATA!$C$2:$C$15>=$D2)*(DATA!$D$2:$D$15>=$C2)+(DATA!$C$2:$C$15>=$C2)*(DATA!$D$2:$D$15>=$D2)>0)*(DATA!$E$2:$E$15>=$E2)*(DATA!$A$2:$A$15=$B2)*DATA!$F$2:$F$15))),DATA!$C$2:$C$15),"")

Report:

Data:

PBI:

File attached for your reference

https://www.dropbox.com/scl/fi/hbrz1w0qglv7oe35vw5sx/NFS-SP-06-12-2023.pbix?rlkey=hifyzqbz5rlkqyly9r...
https://www.dropbox.com/scl/fi/xd8m5cjfux1y0oa1pitxw/SP-06.12.2023.xlsx?rlkey=zvhnmk6q4epu19jshlvhcb...

[Ashish_Mathur]

Hi,

Write these calculated column formulas in the Report table

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[Width]>=EARLIER(REPORT[Pack Length])&&DATA[Height]>=EARLIER(REPORT[Pack Height])&&DATA[Depth]>=EARLIER(REPORT[Pack Width]))))

Rack width = LOOKUPVALUE(DATA[Width],DATA[Area Code],REPORT[Area Code],DATA[Volume],REPORT[Volume])

Rack height = LOOKUPVALUE(DATA[Height],DATA[Area Code],REPORT[Area Code],DATA[Volume],REPORT[Volume])

Rack depth = LOOKUPVALUE(DATA[Depth],DATA[Area Code],REPORT[Area Code],DATA[Volume],REPORT[Volume])

Hope this helps.

[Saxon202202]

@Ashish_Mathur , 
Thank you for your response. While the DAX code functions correctly when dealing with multiple area codes in the report table, it encounters issues when handling specific area codes, resulting in inaccurate outcomes. For instance,when the area code is "AB1" in the report table, the expected volume is 9 or 11.
However, the DAX code returns volumes associated with different area codes.
Among the total 7847 line items in the report table, 5352 align perfectly, but 2492 exhibit incorrect results. Despite my attempts to adjust the DAX code, it continues to produce errors.
incorrect Result:

Can you assist me, please?
I have attached a file for your perusal.
https://www.dropbox.com/scl/fi/qrburmfeh65i8n521r3m6/Lookup2-1-Issue_07-12-2023.pbix?rlkey=o6w19w25w...
https://www.dropbox.com/scl/fi/1um93fj1lp0oxli9k6bpv/SP-07.12.2023.xlsx?rlkey=5n91yq2p85fiiw11k4t3qm...

[Ashish_Mathur · Accepted solution]

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.

[Saxon202202]

@Ashish_Mathur ,
Thank you for your response once more, and I apologize for the delayed reply.
Can you provide a quick tip on including extra filter criteria within your impressive DAX code?
I want the same resut by applying one more additionl filter criteria which is "Mode". If I have more thn one filter criteria can I apply next to area code for both place? It will work? The "Mode" filter criteria need to be apply next to the area code for both place? (Highlighted in red squre.
Data:

Report:

https://www.dropbox.com/scl/fi/npbwfs3w42ifduzfevmgh/Lookup2-1_1312.pbix?rlkey=jejbnod00zrrj2dgchurf...
https://www.dropbox.com/scl/fi/xd8m5cjfux1y0oa1pitxw/SP-06.12.2023.xlsx?rlkey=zvhnmk6q4epu19jshlvhcb...

[Ashish_Mathur]

Yes, you can.  Please try it yourself.

[Saxon202202]

@Ashish_Mathur ,
I appreciate your response. I've followed your suggestion and applied the filter after the type. Could you please confirm if this is the correct method for applying the additional filter criteria?

Volume with Mode = CALCULATE(MAX(DATA[Volume]),FILTER(DATA,(DATA[Area Code]=EARLIER(REPORT[Area Code])&&(DATA[Mode])=EARLIER(REPORT[Mode])&&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[Mode])=EARLIER(REPORT[Mode])&&DATA[Width]>=EARLIER(REPORT[Pack Length])&&DATA[Height]>=EARLIER(REPORT[Pack Height])&&DATA[Depth]>=EARLIER(REPORT[Pack Width]))))

[Ashish_Mathur]

If you get the correct answer, then it is the right formula.

[vanessafvg]

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]

@vanessafvg, thank you for your reply.

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

[vanessafvg]

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?