Forum Discussion

b2wise's avatar
b2wise
Icon for Helper III rankHelper III
5 years ago
Solved

Find Biggest, Middle and Smallest Value

Hi All,   There are two tables in my dataset one for item dimensions and one for box dimensions. I need to write logic for how many of each item can fit in a given box by dividing the largest dimen...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    This seems overly complicated to me. There are only six possible ways to orient a 3D rectangular item relative to a 3D rectangular box (assuming we ignore shifts and diagonal arrangments). Hence, we can have the computer try all of them "brute force" rather than worrying about matching small/medium/large (especially since that may not be the optimal packing anyway).

     

    MaxFit = 
VAR BoxH = SELECTEDVALUE ( Boxes[BOX_HEIGHT] )
VAR BoxW = SELECTEDVALUE ( Boxes[BOX_WIDTH] )
VAR BoxD = SELECTEDVALUE ( Boxes[BOX_DEPTH] )
VAR ItemH = SELECTEDVALUE ( Items[M1HEIGHT] )
VAR ItemW = SELECTEDVALUE ( Items[M1WIDTH] )
VAR ItemL = SELECTEDVALUE ( Items[M1LENGTH] )
VAR Case1 = INT ( BoxH / ItemH ) * INT ( BoxW / ItemW ) * INT ( BoxD / ItemL )
VAR Case2 = INT ( BoxH / ItemH ) * INT ( BoxW / ItemL ) * INT ( BoxD / ItemW )
VAR Case3 = INT ( BoxH / ItemW ) * INT ( BoxW / ItemH ) * INT ( BoxD / ItemL )
VAR Case4 = INT ( BoxH / ItemW ) * INT ( BoxW / ItemL ) * INT ( BoxD / ItemH )
VAR Case5 = INT ( BoxH / ItemL ) * INT ( BoxW / ItemH ) * INT ( BoxD / ItemW )
VAR Case6 = INT ( BoxH / ItemL ) * INT ( BoxW / ItemW ) * INT ( BoxD / ItemH )
RETURN
    MAXX ( { Case1, Case2, Case3, Case4, Case5, Case6 }, [Value] )

     

    In N dimensions, there are N! case permutations and it would be better to generate them more programmatically, but with 3! = 6, it's not worth the bother.

     

    With the data from b2wise, only item 3D-AWPTC-250 fits in any of the given boxes (with V07 able to fit the most).

     

    If we assume items are listed in centimeters and boxes in inches, then things aren't quite so bad (only 3D-HVE can't fit in any box).

     

b2wise's avatar
b2wise
Icon for Helper III rankHelper III
5 years ago

Hi AlexisOlson,

 

Thank you for your solution, the logic makes sense but I'm having trouble recreating the results. I am attempting to put the item name as the row and box type as the column and your formula as the value in a matrix. First I got an error that there was no relationship between the two tables so I made a bogus one based on those two columns. Now I'm getting an error that "An argument of function 'INT' has the wrong data type or the result is too large or too small". I made sure all the datatypes are correct and am not sure why it's not working.

 

Can you share with me how you made it work?

Thanks again!

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi b2wise ,

 

I have the same problem as you when I use the AlexisOlson  measure. Changing "INT" to "TRUNC" I can get the correct result.

 

MaxFit = 
VAR BoxH = SELECTEDVALUE ( Boxes[BOX_HEIGHT] )
VAR BoxW = SELECTEDVALUE ( Boxes[BOX_WIDTH] )
VAR BoxD = SELECTEDVALUE ( Boxes[BOX_DEPTH] )
VAR ItemH = SELECTEDVALUE ( Items[M1HEIGHT] )
VAR ItemW = SELECTEDVALUE ( Items[M1WIDTH] )
VAR ItemL = SELECTEDVALUE ( Items[M1LENGTH] )
VAR Case1 =  TRUNC( BoxH / ItemH ) *  TRUNC( BoxW / ItemW ) *  TRUNC( BoxD / ItemL )
VAR Case2 =  TRUNC( BoxH / ItemH ) *  TRUNC( BoxW / ItemL ) *  TRUNC( BoxD / ItemW )
VAR Case3 =  TRUNC( BoxH / ItemW ) *  TRUNC( BoxW / ItemH ) *  TRUNC( BoxD / ItemL )
VAR Case4 =  TRUNC( BoxH / ItemW ) *  TRUNC( BoxW / ItemL ) *  TRUNC( BoxD / ItemH )
VAR Case5 =  TRUNC( BoxH / ItemL ) *  TRUNC( BoxW / ItemH ) *  TRUNC( BoxD / ItemW )
VAR Case6 =  TRUNC( BoxH / ItemL ) *  TRUNC( BoxW / ItemW ) *  TRUNC( BoxD / ItemH )
RETURN
    MAXX ( { Case1, Case2, Case3, Case4, Case5, Case6 }, [Value] )