LOOKUPVALUE not returning result for matching data

[msylv13]

Hello, 

I am trying to use the LOOKUPVALUE function across two tables to return a result of "Published" or "Unpublished." In data table 'Sales Data' I have a cell value 401211_US - Ferguson that is also found in data table 'Sept 2022 Publication Data'. However, the LOOKUPVALUE function is not returning a result despite having an exact match and no duplicate values that would cause an unexpected result. I have also checked that there are no unwanted spaces as both cell values have LEN of 20. Snapshots of the two tables and DAX expression below:

And the DAX expression:

P_Status = lookupvalue('Sept 2022 Publication Data'[Published],'Sept 2022 Publication Data'[Mat_Distributor],[Mat_Salsify],"Unpublished")

[ryan_mayu]

it looks like the result is correct. the length of 401211_US 0 Ferguson is 20 in both tables.

Could you pls provide the sample data (not in screenshot) and the expected output?

[msylv13]

It is not the expected result as the DAX expression should produce a result of "published." I was able to produce the correct result by changing the relationship between tables to [Mat_Salsify] and [Mat_Distributor]. It would still be good to know why the DAX expression did not produce the desired result before changing the relationship.