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