LOOKUPVALUE not retrieving all the values

Question

&nbsp;Hi, I have two tables. KEYWORD REFERENCE table has a list of abbreviations (unique values) that are classified into different types. KEYWORDS table has a list of users that have been assigned one or more of the abbreviations from the KEYWORD REFERENCE table. I need to retrieve the abbreviation type in the KEYWORDS table. They look something like this:&nbsp;KEYWORD REFERENCEABBREVIATIONTYPEABCTYPE 1DEFTYPE 1HIJTYPE 2KLMTYPE 3NOPTYPE 2QRSTYPE 3&nbsp;KEYWORDSUIDABBREVIATIONTYPE1ABC&nbsp;2ABC&nbsp;3KLM&nbsp;4KLM&nbsp;5KLM&nbsp;6DEF&nbsp;&nbsp;I have a many to one relationsheep between both tables, where the common value is the ABBREVIATION and I am trying to create a formula that will add the TYPE to my KEYWORDS table.&nbsp;I have tried several options I have found in different tutorials; however, the problem is the same in all. The formula works but not for all of the values. Some of the rows are not getting a Type even when they have it in the KEYWORD REFERENCE table, and I am not sure why. I think it has something to do with the fact that my KEYWORDS table has duplicate values in the ABBREVIATIONS column; however, I have not found a way to make it work, as these values belong to a different user; therefore, they must stay like that. I have removed relationships, changed them to one to many and nothing fixes the problem. Can you help?&nbsp;These are the formulas I have tried:&nbsp;KEYWORD TYPE = &nbsp; &nbsp; &nbsp; &nbsp; VAR _type =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LOOKUPVALUE('Keyword Reference'[type],'Keyword Reference'[abbreviation],Keywords[ABBREVIATION])&nbsp; &nbsp; &nbsp; &nbsp; VAR _nokeyword =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LOOKUPVALUE(Keywords[ABBREVIATION],Keywords[ABBREVIATION],"NO KEYWORD")&nbsp; &nbsp; &nbsp; &nbsp; RETURN&nbsp; &nbsp; &nbsp; &nbsp; IF(Keywords[ABBREVIATION] = _nokeyword,"NO KEYWORD", _type)&nbsp;***********************TYPE = LOOKUPVALUE('Keyword Reference'[type],'Keyword Reference'[abbreviation],Keywords[ABBREVIATION])&nbsp;************************&nbsp;TYPE2 = CONCATENATEX(&nbsp; &nbsp; &nbsp; &nbsp;FILTER('Keyword Reference','Keyword Reference'[abbreviation] = Keywords[ABBREVIATION]),&nbsp; &nbsp; &nbsp; &nbsp;'Keyword Reference'[type])&nbsp;************************Thank you in advance.

philiptreacy · Accepted Answer

Hi&nbsp;Mon_Go27&nbsp;
&nbsp;
Download example PBIX file
&nbsp;
Why do you need to create a column in the Keywords table?&nbsp; If you have a relationship between the two tables then the Type can be shown in a table visual when displaying the UID and Abbreviation from the Keyworsd table - see attached file.
&nbsp;

&nbsp;
regards
&nbsp;
Phil

Forum Discussion

LOOKUPVALUE not retrieving all the values

2 Replies

ABBREVIATION	TYPE
ABC	TYPE 1
DEF	TYPE 1
HIJ	TYPE 2
KLM	TYPE 3
NOP	TYPE 2
QRS	TYPE 3