Forum Discussion
Helper IIILookupvalue Return Max Value
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.
Super UserHi,
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.
Helper IIIAshish_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=0
- Ashish_Mathur
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=jejbnod00zrrj2dgchurfde55&dl=0
https://www.dropbox.com/scl/fi/xd8m5cjfux1y0oa1pitxw/SP-06.12.2023.xlsx?rlkey=zvhnmk6q4epu19jshlvhcbx0q&dl=0- Ashish_Mathur
Yes, you can. Please try it yourself.