cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

LOOKUPVALUE does not return all values

Hello to all

 

This is in fact a strange one (or not):

I'm using LOOKUPVALUE with the following data (ITEM):

ID  TID  Previous Status
1   2       A

2   2       B

3   2       C

4   3       A

5   3       B

6   4       A

7   4       B

 

And this one (GROUP):

Status   Group
A          Group1

B          Group1

C          Group2

 

So using this

LOOKUPVALUE(GROUP[group],GROUP[Status],ITEM[Previous Status])

 

I'm getting only value in the first TID...

ID  TID  Previous Status  LOOKUP
1   2       A                         Group1

2   2       B

3   2       C

4   3       A                        Group1

5   3       B

6   4       A                        Group1

7   4       B

 

I have a relation beetween tables in another column.

 

Why is this happening?

 

Thanks

 

 

 

 

9 REPLIES 9
Super User
Super User

Re: LOOKUPVALUE does not return all values

I believe this is by design:

 

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

 

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Anonymous
Not applicable

Re: LOOKUPVALUE does not return all values

Thanks @Greg_Deckler

I also red that.. but for me the values are in fact diferent... I assumed that the behaviour was not the one you describe.

 

So..

 

A Filter/Calculate to get this one?

Super User
Super User

Re: LOOKUPVALUE does not return all values

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: LOOKUPVALUE does not return all values

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Anonymous
Not applicable

Re: LOOKUPVALUE does not return all values

@Greg_Deckler they are related in other field.

 

So I think I can not use what you refer.

 

 

 

 

Anonymous
Not applicable

Re: LOOKUPVALUE does not return all values

Yep... bu I tried the same with a "new" table that withou relation and the result was the same...

 

Highlighted
Super User
Super User

Re: LOOKUPVALUE does not return all values

I attached my file, tables 34 and 35. I'd have to understand how the tables are related to probably tell you how to solve it.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Anonymous
Not applicable

Re: LOOKUPVALUE does not return all values

Thank @Greg_Deckler

After seeing your reply I went again trough all the data and for some weird reason the duplicate table had no data...

 

After doing it by hand.. it worked. But without relation between tables. I would say that we have a bug in the lookup function.

Community Support Team
Community Support Team

Re: LOOKUPVALUE does not return all values

Hi @Anonymous,

 

"I have a relation beetween tables in another column."

I tested such a scenario where there existing a relationship based on another column, but the results returned by LOOKUPVALUE looked as expected.

 

Please show us how did you establish this relationship. Please provide some more detailed repro steps.

 

Regards,

Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.