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

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

Forum Discussion

Help with Look up