Forum Discussion
Returning values from lookup table
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.
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.
1 Reply
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.