Weird Behaviour Lookupvalue - returning results only for select rows

[patrick3]

Hi all,

So I'm using Lookupvalue to populate a column with a particular value, based on 3 other columns.

It works - but only for a very select group of rows. First idea is of course, that the other rows don't have matching values - however, I have some rows with exactly the same values, where some return the right value and other's don't.

Only differentiator I have found is that in the same table I'm populating the new value, in a different column there's a different value.

Hopefully this example helps to explain:
I'm using Product Category, Sub-Category & Created Month in the Lookupvalue, but not Source - yet Source seems to be the differentiator. What could cause this behaviour?

PRODUCT CATEGORY	SUB-CATEGORY	SOURCE	Value of Widgets	Created Month
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Mars	100	Oct-19
Widgets	Widgets from the Universe	Widgets from Mars	100	Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Mars	100	Oct-19
Widgets	Widgets from the Universe	Widgets from Mars	100	Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Mars	100	Oct-19
Widgets	Widgets from the Universe	Widgets from Mars	100	Oct-19
Widgets	Widgets from the Universe	Widgets from Mars	100	Oct-19
Widgets	Widgets from the Universe	Widgets from Mars	100	Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Mars	100	Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Mars	100	Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19
Widgets	Widgets from the Universe	Widgets from Venus		Oct-19

[Anonymous]

Hi @patrick3,

It will help if your share sample pbix file for test.
LOOKUPVALUE function is not suitable to search table with duplicate records(it will return an error message about finding out multiple records), you can consider adding more conditions fields to this function to help search the unique record.
In addition, you can also try to use the filter function to search table records, remove duplicate records and merge these results to one.

Best Regards,

Jack Chen

[patrick3]

Hi @Anonymous , thanks for your reply - the search table has no duplicate records - based on the 3 conditions I'm specifying, there are only unique values and the formula is not returning an error - it's just returning 1 value, but not in all cases where it should.

[Anonymous]

HI @patrick3 ,

Can you please share pbix file with some sample data to test? It will help to find out the reason of lookupvalue function does not work on a specific field.

Regards,

Xiaoxin Sheng