Reduce data by adding new column

[ehrreich21]

I'm trying to reduce the amount of data to meet the following requirement

Can’t have one dimension greater than 23 inches, or 2 dimensions greater than 14 inches, or 3 dimensions greater than 11 inches.

Does anyone know how to make an expression to tell me true or false if it meet this requirement?

I would Also if it's easier to just say my length width and height are not larger than 11 would be even helpful

[jdbuchanan71]

@ehrreich21 

I think this is along the lines of what you are looking for.  It is a calculated column on the table.

Check = 
SWITCH (
    TRUE(),
    'Table'[Length] > 23 || 'Table'[Width] > 23 || 'Table'[Height] > 23, "One over 23",
    ('Table'[Length] > 14 && 'Table'[Width] > 14) || ('Table'[Width] > 14 && 'Table'[Height] > 14) || ('Table'[Height] > 14 && 'Table'[Length] > 14), "Two over 14",
    'Table'[Length] > 11 && 'Table'[Width] > 11 && 'Table'[Height] > 11, "Three over 11",
    "good"
)

[ehrreich21]

I must be doing something wrong as I get errors in mine

I copy and pasted the following: 

Check =
SWITCH (
TRUE(),
'Table'[UOM_LENGTH] > 23 || 'Table'[UOM_WIDTH] > 23 || 'Table'[UOM_HEIGHT] > 23, "One over 23",
('Table'[UOM_LENGTH] > 14 && 'Table'[UOM_WIDTH] > 14) || ('Table'[UOM_WIDTH] > 14 && 'Table'[UOM_HEIGHT] > 14) || ('Table'[UOM_HEIGHT] > 14 && 'Table'[UOM_LENGTH] > 14), "Two over 14",
'Table'[UOM_LENGTH] > 11 && 'Table'[UOM_WIDTH] > 11 && 'Table'[UOM_HEIGHT] > 11, "Three over 11",
"good"
)

Can you add a screen shot of you custom column dialogue box?

[jdbuchanan71]

Is your table named 'Table'?  If not then you have to change my measure to match the name of the table in your model.

Or you can take the table reference out of the formula:

Check = 
SWITCH (
    TRUE(),
    [UOM_LENGTH] > 23 || [UOM_WIDTH] > 23 || [UOM_HEIGHT] > 23, "One over 23",
    ([UOM_LENGTH] > 14 && [UOM_WIDTH] > 14) || ([UOM_WIDTH] > 14 && [UOM_HEIGHT] > 14) || ([UOM_HEIGHT] > 14 && [UOM_LENGTH] > 14), "Two over 14",
    [UOM_LENGTH] > 11 && [UOM_WIDTH] > 11 && [UOM_HEIGHT] > 11, "Three over 11",
    "good"
)

[ehrreich21]

I changed the name but I still get an error, see my screenshot

[jdbuchanan71]

You need to take the 

'Check ='

out of your fomula.  That is what is causing it to not work.

[ehrreich21]

I removed that and still error, not sure what is causing the error everything else should be swapped correct with my names

[jdbuchanan71]

Oh, you are trying to add the column in the query editor which uses a different language (M).  My formula is for adding the column to the model using DAX in the table view.

[ehrreich21]

I had to convert the values to cm but it doesn't seem to work on all, I am highlight an example where it break the rules

Check =

SWITCH (

TRUE(),

'ITEM_MASTER'[UOM_LENGTH] > 58.42 || 'ITEM_MASTER'[UOM_WIDTH] > 58.42 || 'ITEM_MASTER'[UOM_HEIGHT] > 58.42, "One over 58.42",

('ITEM_MASTER'[UOM_LENGTH] > 35.56 && 'ITEM_MASTER'[UOM_WIDTH] > 35.56) || ('ITEM_MASTER'[UOM_WIDTH] > 35.56 && 'ITEM_MASTER'[UOM_HEIGHT] > 35.56) || ('ITEM_MASTER'[UOM_HEIGHT] > 35.56 && 'ITEM_MASTER'[UOM_LENGTH] > 35.56), "Two over 35.56",

'ITEM_MASTER'[UOM_LENGTH] > 27.94 && 'ITEM_MASTER'[UOM_WIDTH] > 27.94 && 'ITEM_MASTER'[UOM_HEIGHT] > 27.94, "Three over 27.94",

"good"

)

[jdbuchanan71 · Accepted solution]

Your check is for a singel value > 58.42 which your hightlighted row is not.

[ehrreich21]

You are correct

[ehrreich21]

That works for adding to the Model which is good, Aside point do you know if it is possible to add to the query editor?

Thanks for all the help