Forum Discussion
Pulling data from multiple tables using multiple relationships
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
12 Replies
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] )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.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
