Calculated Column with LOOKUPVALUE only returns partial values

[wbhite]

Hello all,

I have a fact table with a Calculated Column that looks up an "Account Name" value from a dimention table (I understand that I should have a relationship between the two, but there is another issue that I don't want to tackle yet).  However, for records in the fact table with the exact same lookup value, only a few of them of returning results.  Below is the CC's DAX and a screenshot of the output.  Please let me know if I'm missing something obvious or if you have suggestions.  Thanks!

DAX:

 Account Name = 
LOOKUPVALUE(
Accounts[Account Name],
Accounts[Account #],
'FactTable'[Account #])

Output:

[v-linhuizh-msft · Accepted solution]

I’d like to acknowledge the valuable input provided by rajendraongole1. The initial idea was instrumental in guiding my approach.

Hi @wbhite ,

Based on my testing, consider that it's because there are hidden characters in the Account# of the dim and fact tables.

You can use the EXACT function to compare the two texts to see if they are identical, and if so, return the Account Name and then make adjustments.

Account Name1 = 
IF(
    EXACT(
        'Fact'[Account #], 
        LOOKUPVALUE('Dim'[Account #], 'Dim'[Account #], 'Fact'[Account #])
    ),
    LOOKUPVALUE('Dim'[Account Name], 'Dim'[Account #], 'Fact'[Account #]),
    "null"
)

A comparison of the two measures is as follows:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[rajendraongole1]

Hi @wbhite -As you noted, a relationship between your fact and dimension tables would usually be ideal for this lookup scenario,you can verify if there is any data type mismatches, Look for if there is any hidden characters (leading/trailing spaces) and clean the data if it is available any.check for no duplicate values in the dimension table and no missing values in the fact table's key column.

the issue should be resolved, and your LOOKUPVALUE should return correct results for all matching records.

Hope the information helps. if you can share sample or dummy infomation in excel will analyse it and share.