cancel
Showing results for
Did you mean:
Helper II

## 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 Name M1LENGTH M1WIDTH M1HEIGHT 3D-HRCW-10 11.8 11.2 6.3 3D-HVE 10.2362 5.9843 16.535 3D-AWMCW 7.6378 7.4803 4.8031 3D-AWPCT 8.9764 10.7874 2.7559 3D-AWPTC-250 5.4331 1 2.5 3D-BARB 6.6929 4.8031 7.1654 3D-BRTA 8.8 5.1 2.5 3D-BRTP 8.7402 5.0394 2.3622 3D-BRTQ 8.8189 4.8819 2.4409 3D-BRTS 8.8 5.1 2.5

This is the box dimensions:

 BOX_TYPE BOX_HEIGHT BOX_DEPTH BOX_WIDTH V01 2 4.25 2 V02 4 3 10 V03 2 4.25 4 V04 4 4.25 4 V05 2 6.5 4 V06 4 6.8 4 V07 6 3.25 6 V08 2 8 4 V09 4 8.5 4 V10 4 10 4

1 ACCEPTED SOLUTION
Super User

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 7
Community Support

Hi @b2wise ,

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

Super User

@b2wise I wrote that once. Box Sizes - Microsoft Power BI Community

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Super User

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

Helper II

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!

Community Support

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] )``````

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Super User

The factorial corresponds to the number of dimensions, not the number of boxes or items. As long as we're talking about normal 3D boxes, it's never more than six orientation possibilities per box and item and is super cheap computationally.

It's also easy to adapt what I've proposed to return a yes/no fit check ( if [MaxFit] > 0 ) or find the minimum volume box that fits a particular item ( take the min volume over boxes with [MaxFit] > 0 ).

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors