Returning values from lookup table

[Anonymous]

I am working with a very large enterprise database which has multiple lookup tables. These tables are typically two columns, one for the value's ID and its corresponding name. what is the best approach for returning values from a table like this? Using LOOKUPVALUE is not suitable becuase the lookup value is not scalar and creating many relationships between the lookup table and associated tables is a nuisance. 

[v-xicai · Accepted solution]

Hi @Anonymous ,

If there are existed relationships among these tables, you may create column or measure in Table1 like DAX below using  FIRSTNONBLANK function.

Column1= CALCULATE (FIRSTNONBLANK ( Table2[goal field], 1 ),FILTER ( ALL ( Table2 ), Table2[linked field] = Table1[linked field] )) 


Measure1= CALCULATE (FIRSTNONBLANK ( Table2[goal field], 1 ), FILTER ( ALL ( Table2 ), Table2[linked field] = MAX(Table1[linked field] )))

Best Regards,

Amy 

Community Support Team _ Amy

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