Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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] )
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
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
@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.
I got it! Thank you!
Also we have a few that will be to big and just get a ship label! Thanks again!
Got it, let me take a peek.
@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.