Forum Discussion

patrick3's avatar
patrick3
Helper II
6 years ago

Weird Behaviour Lookupvalue - returning results only for select rows

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 CATEGORYSUB-CATEGORYSOURCEValue of WidgetsCreated Month
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Mars100Oct-19
WidgetsWidgets from the UniverseWidgets from Mars100Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Mars100Oct-19
WidgetsWidgets from the UniverseWidgets from Mars100Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Mars100Oct-19
WidgetsWidgets from the UniverseWidgets from Mars100Oct-19
WidgetsWidgets from the UniverseWidgets from Mars100Oct-19
WidgetsWidgets from the UniverseWidgets from Mars100Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Mars100Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Mars100Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
WidgetsWidgets from the UniverseWidgets from Venus Oct-19
Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      patrick3
      Helper II
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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