Greg_Deckler
5 years agoCommunity Champion
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...
Greg_Deckler
5 years agoCommunity Champion
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.
Anonymous
5 years agoNot applicable
I got it! Thank you!
Also we have a few that will be to big and just get a ship label! Thanks again!