Forum Discussion

wbhite's avatar
wbhite
Helper I
1 year ago
Solved

Calculated Column with LOOKUPVALUE only returns partial values

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 ano...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
rajendraongole1
Super User
1 year ago

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.