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

 

[miguel.escobar]

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

 

[prez02]

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

 

[masplin]

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?

 

[prez02]

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?

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

 

[miguel.escobar]

you should also check this video out The Powerpivot Way - Total All LOOKUPs - YouTube

 

[masplin]

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

 

[masplin]

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

 

[miguel.escobar]

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,

 

[masplin]

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