Add Column LookupValue in a Single Row in Another Table

[Shelley]

Hi All, This seems like it should be very easy, but I must be missing something really simple. I have a number of fact tables connected by a link table on a key made up of customer ID + contract number. However, I want to look up the Contract description by contract number only, not the whole key.

I created the Link or Bridge table like this:

Link Table =

DISTINCT(UNION(

    SELECTCOLUMNS('RepairTransaction',"%BPIDContractKey",'RepairTransaction'[%BPIDContractKey]),

    SELECTCOLUMNS('ServiceTicket',"%BPIDContractKey",'ServiceTicket'[%BPIDContractKey]),

    SELECTCOLUMNS('QBContract',"%BPIDContractKey",'QBContract'[%BPIDContractKey])

    ))

The QBContract table has one row per contract # as contracts or stored with one customer ID even though in actuality in the transactional data, there can be multiple customer IDs per contract. Therefore, there are multiple rows in the Link table for the same contract number. Because the relationship is based on a key combining customer ID with the contract number, when I want to show the contract description in a table, it is always blank when the transaction involves one of the other customer numbers (not the primary one for the contract). As such, I want to add a column in the Link Table for the Contract Description. I should be able to lookup the contract number in the current row of the link table to the QBContracts table (which has a single line per contract), and grab the contract description. However, I cannot get this to work.

Here's my formula, but it is returning the exact same results as just using the description field directly out of the QBContracts table -- Blanks for those records with a different customer ID than the one on the contract.

Contract Description =

LOOKUPVALUE('QBContract'[Contract Description], 'QBContract'[SAP Contract #], 'Link Table'[Contract # Short])

Any ideas? I really appreciate it. See the many to one relationship below.

[Anonymous]

Hi @Shelley ,

Can you please share some sample data for test? It is hard to test without any detail informations.

Notice: do mask on sensitive data before share.
Regards,

Xiaoxin Sheng

[Shelley]

@Anonymous Unfortunately, I don't know of an easy way to do this and mask our data. I'll take another look and see if I can figure something out.