Box Sizes 09-08-2020 21:36 PM - last edited 09-08-2020 21:50 PM

[Greg_Deckler]

Here's a fun one brought to you @ehrreich21 in this thread. Have to admit, I love the business operations stuff the mostest! This handy little guy figures out the optimal box size for you automagically. Not really much more to say about it other than it's actually more complex than you would initially think! The complexity comes in because; trust me, we all know that customers lie and they tell you these lies straight-up to your face without any remose or regret what-so-ever. Like that a height is a width or a width is a length and so forth and so on. But I'm not bitter. Anyway, this was like the 2nd or 3rd time I think I have solved this particular riddle so figured I'd post it here for posterity!

Box Size = 
    VAR __Height = MAX(Orders[UOM_HEIGHT])
    VAR __Width = MAX(Orders[UOM_WIDTH])
    VAR __Length = MAX(Orders[UOM_LENGTH])
    VAR __UserMeasure1 = MINX( {__Height,__Width,__Length},[Value])
    VAR __UserMeasure2 = IF(__Height = __Width && __Height = __Length,__Height,MINX( EXCEPT({__Height,__Width,__Length},{__UserMeasure1}),[Value]))
    VAR __UserMeasure3 = MAXX( {__Height,__Width,__Length},[Value])
    VAR __BoxTable =
        ADDCOLUMNS(
        ADDCOLUMNS(
            ADDCOLUMNS(
            'BoxSizes',
            "__Measure1",MINX( {[Height],[Width],[Length]},[Value])
            ),
            "__Measure2",IF([Height] = [Width] && [Height] = [Length],[Height],MINX( EXCEPT({[Height],[Width],[Length]},{[__Measure1]}),[Value]))
        ),
        "__Measure3",MAXX( {[Height],[Width],[Length]},[Value])
        )
    VAR __Table1 = 
            ADDCOLUMNS(
            __BoxTable,
            "Fit1",IF(__UserMeasure1<=[__Measure1],1,0),
            "Fit2",IF(__UserMeasure2<=[__Measure2],1,0),
            "Fit3",IF(__UserMeasure3<=[__Measure3],1,0)
        )
    VAR __Table = 
        FILTER(
            __Table1,
            [Fit1]=1 && [Fit2]=1 && [Fit3]=1
        )
    VAR __MinBoxVolume = MINX(__Table,[Volume])
    RETURN
    MAXX(FILTER(__Table,[Volume]=__MinBoxVolume),[Size])

eyJrIjoiMGRhM2NkZWQtMjEzNy00NDNmLWIxYzItMWMyYjc0YWI5Zjg2IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[AlexisOlson]

The concept is nice but the code for the middle value left me wanting a better way.

Here's a modification that uses TOPN to sort the measures:

Box Size = 
VAR __Height = MAX ( Orders[UOM_HEIGHT] )
VAR __Width  = MAX ( Orders[UOM_WIDTH] )
VAR __Length = MAX ( Orders[UOM_LENGTH] )
VAR UserMeasures = SELECTCOLUMNS ( { __Height, __Width, __Length }, "__UserMeasure", [Value] )
VAR MeasureRanks = SELECTCOLUMNS ( { 1, 2, 3 }, "__Rank", [Value] )
VAR BoxTable =
    ADDCOLUMNS (
        BoxSizes,
        "__Fit",
            VAR BoxMeasures = SELECTCOLUMNS ( { [Height], [Width], [Length] }, "__BoxMeasure", [Value] )
            VAR MatchRanks = /* Align measures in descending order */
                ADDCOLUMNS (
                    MeasureRanks,
                    "__BoxMeasure",  MINX ( TOPN ( [__Rank], BoxMeasures,  [__BoxMeasure]  ), [__BoxMeasure] ),
                    "__UserMeasure", MINX ( TOPN ( [__Rank], UserMeasures, [__UserMeasure] ), [__UserMeasure] )
                )
            RETURN
                MINX ( MatchRanks, IF ( [__UserMeasure] <= [__BoxMeasure], 1, 0 ) )
    )
VAR __MinBoxVolume = MINX ( FILTER ( BoxTable, [__Fit] = 1 ), [Height] * [Width] * [Length] )
RETURN
    MAXX ( FILTER ( BoxTable, [Volume] = __MinBoxVolume ), [Size] )

[ehrreich21]

@Greg_Deckler 

I can't open your files as our powerbi is an older version. Anyway to save it down. Otherwise, I tried pasting it into mine and it shows nothing in the box size column, but I get no errors either. Also my file is to big to attach

[ehrreich21]

@Greg_Deckler 

My file attached, I removed some stuff, but also this is older version so I can't be saved In new or I can't open it

[Greg_Deckler]

@ehrreich21 - OK, first, you will need to create this column in BoxTable:

Volume = [Height]*[Length]*[Width]

Then you need to change your column formula to this in ITEM_MASTER (you do have some items that don't fit)

Box Size = 
    VAR __Height = ITEM_MASTER[UOM_HEIGHT]
    VAR __Width = ITEM_MASTER[UOM_WIDTH]
    VAR __Length = ITEM_MASTER[UOM_LENGTH]
    VAR __UserMeasure1 = MINX( {__Height,__Width,__Length},[Value])
    VAR __UserMeasure2 = IF(__Height = __Width && __Height = __Length,__Height,MINX( EXCEPT({__Height,__Width,__Length},{__UserMeasure1}),[Value]))
    VAR __UserMeasure3 = MAXX( {__Height,__Width,__Length},[Value])
    VAR __BoxTable =
        ADDCOLUMNS(
        ADDCOLUMNS(
            ADDCOLUMNS(
            'BoxTable',
            "__Measure1",MINX( {[Height],[Width],[Length]},[Value])
            ),
            "__Measure2",IF([Height] = [Width] && [Height] = [Length],[Height],MINX( EXCEPT({[Height],[Width],[Length]},{[__Measure1]}),[Value]))
        ),
        "__Measure3",MAXX( {[Height],[Width],[Length]},[Value])
        )
    VAR __Table1 = 
            ADDCOLUMNS(
            __BoxTable,
            "Fit1",IF(__UserMeasure1<=[__Measure1],1,0),
            "Fit2",IF(__UserMeasure2<=[__Measure2],1,0),
            "Fit3",IF(__UserMeasure3<=[__Measure3],1,0)
        )
    VAR __Table = 
        FILTER(
            __Table1,
            [Fit1]=1 && [Fit2]=1 && [Fit3]=1
        )
    VAR __MinBoxVolume = MINX(__Table,[Volume])
    RETURN
        MAXX(FILTER(__Table,[Volume]=__MinBoxVolume),[Size])

Part of the issue is you were using the measure form in a column form, but makes sense since you couldn't open the PBIX to see the column form.

[ehrreich21]

@Greg_Deckler 

I got it! Thank you!

Also we have a few that will be to big and just get a ship label! Thanks again!

[Greg_Deckler]

Got it, let me take a peek.

[Greg_Deckler]

@ehrreich21 Hmm, here is my BoxSizes table, that's the name:

Orders table

Hopefully that helps, maybe you could send me/post a PBIT and I could take a look and see? Not sure if that will work.