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 dimension by the largest dimension, middle dimension by middle dimension and smallest dimension by smallest dimension.

So find the box dimension which is greatest (Depth, Width, or Height) and divide by the greatest item dimension (Length, Width, or Height) and so on for the middle value and the smallest value.

How would I write that in DAX? 

 

This is the item dimensions:

3D Item NameM1LENGTHM1WIDTHM1HEIGHT
3D-HRCW-1011.811.26.3
3D-HVE10.23625.984316.535
3D-AWMCW7.63787.48034.8031
3D-AWPCT8.976410.78742.7559
3D-AWPTC-2505.433112.5
3D-BARB6.69294.80317.1654
3D-BRTA8.85.12.5
3D-BRTP8.74025.03942.3622
3D-BRTQ8.81894.88192.4409
3D-BRTS8.85.12.5

 

This is the box dimensions:

 

BOX_TYPEBOX_HEIGHTBOX_DEPTHBOX_WIDTH
V0124.252
V024310
V0324.254
V0444.254
V0526.54
V0646.84
V0763.256
V08284
V0948.54
V104104

 

Thanks in advance!

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).

     

7 Replies

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      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).

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        5 years ago

        AlexisOlson Could be, but it would be interesting to know if 10 slightly more complicated calculations runs faster or slower than 10! slightly simpler calculations. Also depends on if you want a matrix of options or just the answer.

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

    Hi b2wise ,

     

    Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

     

    Best Regards,
    Winniz