Specific lookup value from a table on the many side in a one to many relationship

[CazzR1]

Dear all,

Please consider the following situation.

I have two tables related to each other via one field. It is a one to many relationship. Let's call the table on the one side 'singleTable' and on the many side 'manyTable' and the linked field in the relationship 'link'.

In singleTable I would like to return a field from manyTable called 'returnValue', based on the relationship that exist via ‘link’. However, as it is a one to many relationship, multiple matching ‘returnValue’s exist and they are non-unique.
Thus, I understand why =RELATED(returnValue) gives an error.
Let’s say I would like to return the n-th value of the multiple matching ‘returnValue’s. What do we do? My idea is to use =RELATEDTABLE(manyTable), however this returns a table whereas I need a single value. How do I get the intersection of n-th row and the column with name ‘returnValue’?

Many thanks in advance.

 

[jruotolo]

Do you have a sample workbook?

 

[Tianbas]

Have a look at this post on PowerPivot.com which describes a method to find a value based on a matchlist.

“CONTAINSX” Revisited: What WAS the Match? « PowerPivotPro

 

[scottsen]

hmmm... so, obviously its easier to grab the min/max/avg, but you should be able to grab the max of the TOPN (or min of the TOPN, depending on ascending/descending desires). Just beware that TOPN is will return more rows than you expect if there are ties.

 

[CazzR1]

Dear all, thanks for the replies so far, I can't respond in detail now but would like to express my gratitude. @scottsen , the return value is a string .

 

[CazzR1]

Dear all,

Finally, please see the following example file:


http://we.tl/0YStsMhrTT

What I'm looking for is a formula for column Salesperson_firstlocation, which should be the first matching location in table Orders for that salesperson . (right now it's data but it should be a calculated field)

I'm also looking for a formula that does the same for the second matching location, third, n-th.....

Thanks a lot in advance.

 

[scottsen]

This is one of those times i struggle with just.. helping answer the question... vs asking "What in the what!?"

If you put this on your salesperson table, you will get the lowest order number associated with that saleperson (and from there, you can look up the associated city).

If you create these measures:
First:=CALCULATE(MAX(Orders[Order_ID]), TOPN(1, Orders, Orders[Order_ID], 1))
Second:=CALCULATE(MAX(Orders[Order_ID]), TOPN(2, Orders, Orders[Order_ID], 1))
Third:=CALCULATE(MAX(Orders[Order_ID]), TOPN(3, Orders, Orders[Order_ID], 1))

You could then create a calc column that is just [First] (etc) to see that it more or less kinda sorta works. (ties will be weird, and when you have a Third with just 2 orders... you will get a repeat).

Still, I am super wondering WHY IN THE WORLD you need this...?

 

[miguel.escobar]

this should work if you want to have THAT specific column as a formula:

=LOOKUPVALUE(Orders[Location],Orders[Salesperson],[Salesperson],Orders[Order_ID],MINX( RELATEDTABLE( Orders),Orders[Order_ID]) )