Help with Look up

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.

Need Help

sevenhills

4 years ago

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

4 Replies