LOOKUPVALUE results are not consistent for the same input

[angieleman]

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?

[angieleman]

@Greg_Deckler EEEEKKKKK I WORKED!!!!!!! YOU'RE MY ABSOLUTE HERO! 

[Greg_Deckler · Accepted solution]

@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.