topic Re: Box Sizes in Quick Measures Gallery

Box Sizes

Greg_Deckler — Wed, 09 Sep 2020 04:50:54 GMT

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

eyJrIjoiMGRhM2NkZWQtMjEzNy00NDNmLWIxYzItMWMyYjc0YWI5Zjg2IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: Box Sizes

Anonymous — Wed, 09 Sep 2020 13:44:20 GMT

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

Re: Box Sizes

Greg_Deckler — Wed, 09 Sep 2020 13:49:22 GMT

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.

Re: Box Sizes

Anonymous — Wed, 09 Sep 2020 13:52:04 GMT

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

Re: Box Sizes

Greg_Deckler — Wed, 09 Sep 2020 13:53:13 GMT

Got it, let me take a peek.

Re: Box Sizes

Greg_Deckler — Wed, 09 Sep 2020 14:03:25 GMT

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.

Re: Box Sizes

Anonymous — Wed, 09 Sep 2020 14:18:30 GMT

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!

Re: Box Sizes

AlexisOlson — Thu, 05 Aug 2021 13:20:03 GMT

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