Looking for only values in a non related table

[Anonymous]

I’ve been trying to use the LOOKUP function but can’t seem to get it to work.  I have 2 tables that do not have a relationship and am trying to get only the Request Id’s (Table 1) are found in the FM Request ID (Table 2).

Table1                                             Table2

Request_ID                                     FM_Request_ID

Thanks,

DK

[v-zhangti · Accepted solution]

Hi, @Anonymous 

I briefly simulated some data that I hope fits your situation.

Column:

Column = 
LOOKUPVALUE('Table 2'[Value],'Table 2'[ FM_Request_ID],[Request_ID])

Does this match the output you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous I tend to use MAXX(FILTER(...),...) in these situations, like:

Measure =
  VAR __ID = MAX('Table1'[Request_ID])
  VAR __T2ID = MAXX(FILTER('Table2',[FM_Request_ID] = __ID),[FM_Request_ID)
RETURN
  IF(ISBLANK(__T2ID),BLANK(),1)

Alternatively, you could do this:

Measure = 
  VAR __ID = MAX('Table1'[Request_ID])
  VAR __FMIDs = SELECTCOLUMNS('Table1',"__FM_Request_ID",[FM_Request_ID])
RETURN
  IF(__ID IN __FMIDs,1,BLANK())