Forum Discussion

angieleman's avatar
angieleman
Icon for Advocate I rankAdvocate I
5 years ago
Solved

LOOKUPVALUE results are not consistent for the same input

I'm seeing inconsistent results with Lookupvalue and need another couple sets of eyes.

 

Table 1 (source) - This table starts as a Reference table in Power Query and is then Grouped in M on the primary column "CR Number", so when it is loaded, the values in the "CR Number" column are unique by default and is not a caculated column. The "CR" column is a whole number. I then have 3-4 calculated columns in this table that I'd like to pull into another table.

 

Table 2 (destination) - This is the DAX UNION of two other tables, and the "Absolute CR" column is also a whole number and is one of the SELECTCOLUMNS pulled in with the union. These two tables are related on the "CR" columns in a one to many relationship.

 

RPM ID = LOOKUPVALUE('Project Summary'[Absolute RPM ID],'Project Summary'[CR Number],[Absolute CR])
 
The weird thing is that sometimes (rarely) I get the correct value pulled back, and other times I get a blank, like the lookupvalue can't filter Table 1 appropriately FOR THE SAME CR NUMBER (I'm screaming). I've spent 3 days trying different formulas instead of LOOUPVALUE. Any Calculate formulas I try don't filter appropriately either.
 
Screenshot shows results in Table 2. First column is formula above. Second column is the same, but pulling back a different result column. And the third is just RELATED to show what's in the CR Number column for the sake of brevity.
 

 

I'm on version April 2021. Any ideas?

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    angieleman I have also struggled with LOOKUPVALUE. I tend to use MAXX(FILTER(...)...) syntax instead. Works regardless if one or more values are pulled back and seems to work more consistently than LOOKUPVALUE when there should be only 1 row anyway. I'm not certain because I haven't really dug into it but I wonder if LOOKUPVALUE has an issue when a blank row appears and since this counts as having more than 1 row in the results, it ends up returning the default blank when more than one row result from the filters provided. It's just a guess but, as I said, MAXX(FILTER takes care of it.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    angieleman I have also struggled with LOOKUPVALUE. I tend to use MAXX(FILTER(...)...) syntax instead. Works regardless if one or more values are pulled back and seems to work more consistently than LOOKUPVALUE when there should be only 1 row anyway. I'm not certain because I haven't really dug into it but I wonder if LOOKUPVALUE has an issue when a blank row appears and since this counts as having more than 1 row in the results, it ends up returning the default blank when more than one row result from the filters provided. It's just a guess but, as I said, MAXX(FILTER takes care of it.