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

[masplin]

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

=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.

Thanks for any advice

Mike

 

[masplin]

For what it is worth i managed ot construct a single measure by using the RANKX function on the LEN(column name) where rank =1. However after 4 hours calculating and using 15GB of RAM I gave up. 160,000 rows with 80,000 unique customers was just too much!!!! If any one has an intelligent idea that would be great, but for time being I think I create a RANKX function for each field (which has the advantage of being to deal with ones the same length).

 

[masplin]

This was my solution that didnt work

=IFERROR(
CALCULATE(
VALUES(Invoices[Unique Customer Code]),
filter(
Invoices,
RANKX(
FILTER(
invoices,
Vehicles[Reg Num Clean]=Invoices[Reg Num Clean]
),
Invoices[Unique Customer Code],,1
)=1
)
),
blank()
)

 

[miguel.escobar]

is there any way that you can upload a copy of the workbook? it's hard for me to imagine how your table looks right now and the values that go with each column

 

[masplin]

sure. I made a mini one as mine is 120MB!. What's the best place to upload? i use dropbox?