Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

Box Sizes

Here's a fun one brought to you Anonymous 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])

 

 

 

Other

7 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    5 years ago

    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] )
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

     

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      5 years ago

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

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        5 years ago

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