LookupValue not returning all values

[ExcelMonke]

I've come across an interesting behaviour with the LOOKUPVALUE DAX, where it is not returning all lookupvalues, even though it should. Has anyone seen this before? Effectively, I am using the LOOKUPVALUE to confirm if a value exists in both tables. (e.g. Does value 123, exist in Table A and in Table B). This works for tens of thousands of rows flawlessly.

However, the other day I stumbled on a couple values where they exist in both tables, but LOOKUPVALUE is not picking them up. This confirmed after I switched to a IF(TableA[Value], IN VALUES (TableB[Value]), 1, 0). The Lookupvalue formula gives me a total of 45,107 "matches", whereas the IF Formula gives me 45,466 "matches". A nearly 1% difference. Does anyone know what may be causing this behaviour? I have tried changing data type, etc. etc. and cannot figure out why lookupvalue is giving me blanks

[Anonymous]

Hi @ExcelMonke ,

Could you please provide more details about issue? Such as formulas and screenshots. It would be better to provide a demo file.

Best regards,

Mengmeng Li

[lbendlin · Accepted solution]

What made you use LOOKUPVALUE in the first place if you have other options?  It should only ever be used as a last resort.

LOOKUPVALUE – DAX Guide

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.