Forum Discussion
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 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=hifyzqbz5rlkqyly9rpzvqyl6&dl=0
https://www.dropbox.com/scl/fi/xd8m5cjfux1y0oa1pitxw/SP-06.12.2023.xlsx?rlkey=zvhnmk6q4epu19jshlvhcbx0q&dl=0
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.
10 Replies
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.
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=o6w19w25wsks4jiorgwnwyefm&dl=0
https://www.dropbox.com/scl/fi/1um93fj1lp0oxli9k6bpv/SP-07.12.2023.xlsx?rlkey=5n91yq2p85fiiw11k4t3qm14s&dl=0Hi,
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.
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?
vanessafvg, thank you for your reply.
Basically the small pack data need to be found smaller rack rather than going to bigger rack.
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?
