Forum Discussion
LOOKUPVALUE not returning result for matching data
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:
3 Replies
Similar problem here where despite using the same search data in the same calculated column, LOOKUPVALUE returned the correct data for some rows and blank value for other rows, what was driving me crazy.
After several try-error tests I found out that the table where LOOKUPVALUE was searching the data was affected by a filter in the data model from a third table, so solved the issue replacing Lookupvalue and using CALCULATE with ALLSELECTED to remove filters from the third table.
Must say I haven't found any documentation of this behaviour of model filters propagating in calculated columns. Thought it only happened in measures (and visuals and RLS, of course)
ryan_mayuSuper User
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?
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.
