LOOKUPVALUE does not return all values

[Anonymous]

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

[Anna_O]

Did you find a solution to this? I have the same issue where I know for a fact that there are values to be found, yet LOOKUPVALUE randonly returns blank values in some rows.

[v-yulgu-msft]

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

[Greg_Deckler]

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.

[Anonymous]

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?

[Greg_Deckler]

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?

[Anonymous]

@Greg_Deckler they are related in other field.

So I think I can not use what you refer.

[Greg_Deckler]

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]

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

[Greg_Deckler]

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.

[Anonymous]

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.