# Is it possible to calculate values based on length of field?

#### masplin

##### Active Member
I have a table of invoices in which a customer may appear 5 times. They may have their name as "K Smith" or "Kate Smith". I have a "customer" table and trying to filter values from the invoice table using this sort of function
Code:
``=CALCULATE(VALUES(Invoices[Full Name]),filter(Invoices,Invoices[Unique Customer Code]=Customers[Unique Customer Code]))``
Obviously this throws an error that a table of multiple values has been supplied.

for dates you can use a MIN or MAX function to pick out one date where there are multiple values.

Is there any way to pick out the longest entry where there are multiple values so the above would return "Kate Smith"? Generally it seems in my data the longer the entry the better it is.

Mike

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
LEN, SUMMARIZE, ADDCOLUMNS, FILTER....even GENERATE could throw something that would help you. Bumping this so we can get Jacob and Laurent to post too! =D sounds like an interesting case

on the fly, what about a new column len(Full name) and adding a max condition with && to your existing filter condition? the problem will be same length of name fields.....

I thought of that, but wont the MAX(Invoices [Full Name Length]) return the length e.g. 8 and not the actual Full Name eg. Mr Smith? I believe MAX in DAX is not like MAX in Excel so it gives you max of a column. If you have a suggested syntax I 'll try it?

I thought of that, but wont the MAX(Invoices [Full Name Length]) return the length e.g. 8 and not the actual Full Name eg. Mr Smith? I believe MAX in DAX is not like MAX in Excel so it gives you max of a column. If you have a suggested syntax I 'll try it?

Code:
``````CALCULATE(
VALUES(Invoices[Full Name]),
filter(Invoices,
Invoices[Unique Customer Code] = Customers[Unique Customer Code] &&
Invoices[Full Name Length] = Max(invoices[Full Name Length])
)
)``````

Yes, Max(invoices[Full Name Length]) returns the max length, but that is then used to filter invoices table, and then values(invoices[full Name]) returns the corresponding Name.

The Problem is, if you have two names that have the same length, this will also return several values.

hthab
Carsten

ah that looks good. yes have that to contend with, but its a start. Thanks.

Just having a go at this and need to do this with bout 8 fields. Is it possible to put the MAX and the LEN within the same expression to avoid creating a load more columns? I tried the condition

LEN(invoices[full name])=CALCULATE(MAX(LEN(invoices[full name)),filter(invoices,invoices[unique customer code]=customers[unique customer code]))

but doesn't like it. as guess MAX only operates on a column. Just concerned that more columns has a horrible effect on speed

Mike

well, in this particular case you'll need to read more about what the row context is as you're trying to get the MAX LEN of an entire column to be computed and then to be used in a row level computation (it sounds more complex than it really is). I barely have any free time nowadays but try checking out the EARLIER and EARLIEST functions that will help you with this.

In case you need more help I'll try my best and if you happen to have workbook that you could upload that would be perfect.

Best,

I know how you feel! I'll give some more thought as seems I need to create a column that MAX can operate on. tHanks

Replies
0
Views
316
Replies
3
Views
432
Replies
0
Views
573
Replies
1
Views
2K
Replies
7
Views
7K

1,196,073
Messages
6,013,269
Members
441,758
Latest member
Abren

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back