LOOKUPVALUE does not return all values

Question

Hello to all&nbsp;This is in fact a strange one (or not):I'm using LOOKUPVALUE with the following data (ITEM):ID&nbsp; TID&nbsp; Previous Status1&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A2&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B3&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; C4&nbsp;&nbsp; 3 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A5&nbsp;&nbsp; 3 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B6&nbsp;&nbsp; 4 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A7&nbsp;&nbsp; 4 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;And this one (GROUP):Status&nbsp;&nbsp; GroupA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Group1B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Group1C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Group2&nbsp;So using thisLOOKUPVALUE(GROUP[group],GROUP[Status],ITEM[Previous Status])&nbsp;I'm getting only value in the first TID...ID&nbsp; TID&nbsp; Previous Status&nbsp; LOOKUP1&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Group12&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B3&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; C4&nbsp;&nbsp; 3 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Group15&nbsp;&nbsp; 3 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B6&nbsp;&nbsp; 4 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Group17&nbsp;&nbsp; 4 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;I have a relation beetween tables in another column.&nbsp;Why is this happening?&nbsp;Thanks&nbsp;&nbsp;&nbsp;&nbsp;

greg_deckler · Answer

I believe this is by design:
&nbsp;
https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax
&nbsp;
Return value
The value of result_column at the row where all pairs of search_column and search_value have a match.
If there is no match that satisfies all the search values, a BLANK is returned. In other words, the function will not return a lookup value if only some of the criteria match.
If multiple rows match the search values and in all cases result_column values are identical then that value is returned. However, if result_column returns different values an error is returned.

anonymous · Answer

Thanks&nbsp;Greg_DecklerI also red that.. but for me the values are in fact diferent... I assumed that the behaviour was not the one you describe.&nbsp;So..&nbsp;A Filter/Calculate to get this one?

greg_deckler · Answer

Are the 2 tables related? Seems like if you relate them ITEM[Previous Value] &gt; GROUP[Status] that this problem becomes trivial. Or are these 2 tables already related some other way?

greg_deckler · Answer

Even more perplexing, I implemented your formula as a Column in ITEM table and it worked perfectly, got all of the group names. This was with completely unrelated tables. So, I'm guessing that you do in fact have a relationship between the tables and that is what is messing with your formula.

anonymous · Answer

Greg_Deckler&nbsp;they are related in other field.&nbsp;So I think I can not use what you refer.&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

LOOKUPVALUE does not return all values

10 Replies

Return value

Related content

LOOKUPVALUE returns blank value

Lookupvalue Return Max Value

LOOKUPVALUE with multiple return values

Lookupvalue Returning an Error

Need to return first value when LOOKUPVALUE returns multiple values