Help with Look up

[BILearn]

Hi all,

I am trying to look up in Power BI, Sample file here (Link updated)

I need to lookup the column "location" from the 'Lookup Table' to 'My Table'

The Video ID is used to link between two tables. However the video ID has duplicates in it.

Question- How can I create a new column 'Location' in My Table and lookup the location values from Lookup Table that has the lowest Sequence number.( Sequence number can be random)

Also keen to know how to do the opposite(location that has highest sequence number)

Can you please help.

Thanks in advance.

[BILearn]

Thank you. @sevenhills , I'll try your solution.

Also Thanks @aj1973 .

Please see the updated link in the original post.

[sevenhills · Accepted solution]

Not able to download the file easily ...  

I came up with these DAX for the column

1) Error! obvisouly, but presenting to you

Column 2 = LOOKUPVALUE('Lookup Table'[Location],'Lookup Table'[Video ID], 'My Table'[Video ID])

2) Random - first non blank lookup 

Column = CALCULATE( FIRSTNONBLANK('Lookup Table'[Location], 1), FILTER(all('Lookup Table'), 'Lookup Table'[Video ID] = 'My Table'[Video ID]))

3) To get the top 1 location value, sorted by sequence number descending 

Column 3 = 
var _c = 'My Table'[Video ID]
var _rv = SELECTCOLUMNS( TOPN(1, CALCULATETABLE('Lookup Table','Lookup Table'[Video ID] = _c), 'Lookup Table'[Sequence Number], 0 ), "Location", 'Lookup Table'[Location])

return _rv 

4) To get the location value, sorted by sequence number ascending

Column 4 = 
var _c = 'My Table'[Video ID]
var _rv = SELECTCOLUMNS( TOPN(1, CALCULATETABLE('Lookup Table','Lookup Table'[Video ID] = _c), 'Lookup Table'[Sequence Number], 1 ), "Location", 'Lookup Table'[Location])

return _rv 

[BILearn]

@sevenhills Thank you, much appreciate your help.

[aj1973]

Hi @BILearn 

The download needs a login and password, Can you share it publicly through other service please?