Forum Discussion
Box Size help
I have a column for UOM_LENGTH, UOM_WIDTH, UOM_HEIGHT with a large set of data, I want to see what box size each item would ship in, with the below table showing the available sizes. My goal is the product would ship in the box based on the measurements. The length, width, and height do not correlate so since it's user inputted. I wanted to add a new column to the dataset that will tell me the box size with the letter. Any help would be greatly appreciated.
Box Sizes ( all in pallet counts) Box Type
9.5x6.5x3 - F
15 X 10 X 4 - G
8x8x4 - H
12x8x4 - I
12x8x8 - J
19x14x4 - L
19x14x8 - N
19x14x12 - P
24 X 14 X 4 - U
24x16x16 - S
Anonymous - Eh, got bored, just went ahead and did it:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Box-Sizes/td-p/1356883
That DAX attempt without testing from memory was trash anyway.
4 Replies
Anonymous - I solved this once before, which I could find that formula, I believe you will need height, width and length in separate columns along with box size. You would then want a Volume calculated column like this:
Volume (Column) = [Height] * [Width] * [Length]Then you need to do this.
Box Size (Column) = VAR __UserMeasure1 = MINX( {[userheight],[userwidth],[userlength]},[Value]) VAR __UserMeasure2 = MINX( EXCEPT({[userheight],[userwidth],[userlength]},{__Measure1}),[Value]) VAR __UserMeasure3 = MINX( EXCEPT({[userheight],[userwidth],[userlength]},{__Measure1, __Measure2}),[Value]) VAR __BoxTable = ADDCOLUMNS( ADDCOLUMNS( ADDCOLUMNS( 'Box Sizes', "__Measure1"=MINX( {[Height],[Width],[Length]},[Value]), ), "__Measure2"=MINX( EXCEPT({[Height],[Width],[Length]},{[__Measure1]}),[Value]), ), "__Measure3"=MINX( EXCEPT({[Height],[Width],[Length]},{[__Measure1],[__Measure2]}),[Value]) ) VAR __Table = FILTER( ADDCOLUMNS( __BoxTable, "Fit1",IF(__UserMeasure1<[__Measure1],1,0), "Fit2",IF(__UserMeasure2<[__Measure2],1,0), "Fit3",IF(__UserMeasure3<[__Measure3],1,0) ), [Fit1]=1 && [Fit2]=1 && [Fit3]=1 ) VAR __MinBoxVolume = MINX(__Table,[Volume]) RETURN MAXX(FILTER(__Table,[Volume]=__MinBoxVolume),[Size])Anonymous - Paste that data as text in a table and I'll write up a specific implementation for you. Are you showing user transactions table there? I will need that. You will also want a table with your box sizes broken out into height, width and length. I hate typing data. Please make sure to @ me in response otherwise I'll probably lose the thread.