cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Reduce data by adding new column

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Reduce data by adding new column

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

2020-06-02_13-50-26.jpg

View solution in original post

11 REPLIES 11
Highlighted
Super User IV
Super User IV

Re: Reduce data by adding new column

@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"
)

2020-06-02_12-33-35.jpg

Highlighted
New Member

Re: Reduce data by adding new column

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?

Highlighted
Super User IV
Super User IV

Re: Reduce data by adding new column

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"
)
Highlighted
New Member

Re: Reduce data by adding new column

 

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

 

Capture.JPG

Highlighted
Super User IV
Super User IV

Re: Reduce data by adding new column

You need to take the 

'Check ='

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

2020-06-02_13-11-15.jpg

 

Highlighted
New Member

Re: Reduce data by adding new column

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

 

Capture2.JPG

Highlighted
Super User IV
Super User IV

Re: Reduce data by adding new column

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.

2020-06-02_13-19-31.jpg

Highlighted
New Member

Re: Reduce data by adding new column

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

Highlighted
New Member

Re: Reduce data by adding new column

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

 

 

Capture3.JPG

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors