Pulling data from multiple tables using multiple relationships

[dmf2022ecc]

Hi

I have a table set up as below

ID     PartnerID

1       6

2       7

3       8

4       

5       

6

7       9

8       10

Their details are stored in a separate table as follows

ID      Name

1        J.Bloggs

2        M.Fakename

6        R.Pop

7        T.Hey

There is an active relationship between ID in the first table, and ID in the second table, and an inactive relationship between PartnerID in the first table, and ID in the second table.

I have a requirement to create a column in the first table bringing back the name for the partner but everything I've tried only brings back blank columns.

For clarity, the end product of the column should be:

ID     PartnerID     PartnerName

1       6                  R.Pop

2       7                  T.Hey

Can someone please show me the (presumably) easy fix that I'm missing! 🙂

Many thanks

[johnt75]

You can't really use inactive relationships in calculated columns, its way too complex. Best to just do a LOOKUPVALUE

Partner Name =
LOOKUPVALUE ( 'Table 2'[Name], 'Table 2'[ID], 'Table 1'[PartnerID] )

[dmf2022ecc]

Hey, thank you. As part of my attempts, I did try:

LOOKUPVALUE ( Table2[Name], Table2[ID], Table1[PartnerID] )
and it returned an empty column again.

[johnt75]

Couple of things to check. First, make sure that there are only unique values in Table 2. If there are any duplicates then it won't work. 

Second, check that the data in the 'Table 1'[partner ID] exactly matches the data in 'Table 2'[ID] - no additional whitespace etc. You could create a new table like

tmp table =
ADDCOLUMNS (
    VALUES ( 'Table 1'[Partner ID] ),
    "@num rows",
        VAR CurrentPartner = 'Table 1'[Partner ID]
        RETURN
            CALCULATE (
                COUNTROWS ( 'Table 2' ),
                TREATAS ( { CurrentPartner }, 'Table 2'[ID] )
            )
)

You should see 1 in the num rows column for each partner

[dmf2022ecc]

Hi thank you again for responding.
Table 2 is indeed a unique key as it is used for many relationships.
The PartnerID does not have any additional whitespaces, they are absolute matches.

Many thanks

[johnt75]

does the temporary table show the correct results ?

[dmf2022ecc]

Yes it does

[johnt75]

Then I can't see why LOOKUPVALUE wouldn't work, sorry.

[dmf2022ecc]

If Table1 PartnerID has BLANKS would that affect anything?

[johnt75]

Obviously you would get a blank name for those rows, but it shouldn't affect the rows where you do have a valid partner ID. Are you sure that the column is completely blank and its not just that the first few rows you see are ones with a blank partner ID ?

[dmf2022ecc]

So the temp table brought through as expected a row for every PartnerID and then a count of 1, with the addition of a row with a blank PartnerID and a blank count.

[johnt75]

You could try

Partner Name =
VAR CurrentPartner = 'Table 1'[Partner ID]
RETURN
    CALCULATE (
        VALUES ( 'Table 2'[Name] ),
        TREATAS ( { CurrentPartner }, 'Table 2'[ID] )
    )

[dmf2022ecc]

Really appreciate your efforts with this.
Another BLANK column unfortunately, so will try and rework the requirements!

Really appreciate your time with this, thank you