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 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:

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!

2 Replies

  • 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.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    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!