Creating relationship from 2 tables

[Samantha]

Hi, I am trying to create a relationship between these 2 tables by linking and it just don't link. I have no problem in linking using other applications.

ARCUSO - [OPTFIELD] ---> CSOPTFD [OPTFIELD]

ARCUSO - [VALUE] ---> CSOPTFD [VALUE]

What is the best way to do?

Any help is much appreciated.

[parry2k · Accepted solution]

@Samanthayou cannot create relationship on two fields, best way is to add new calculate column in both table by concatenating these two fields  fields and then set the relation on this new calculated columns. hope it is clear.

[v-yulgu-msft · Accepted solution]

Hi @Samantha,

Agree with parry2k, to create a relationship based on multiple fields, you may need to combine them first.

Please create a calculated column in both 'ARCUSO' and 'CSOPTFD'.

ColumninARCUSO='ARCUSO'[OPTFIELD] & " " & 'ARCUSO'[VALUE]

ColumninCSOPTFD='CSOPTFD'[OPTFIELD] & " " & 'CSOPTFD'[VALUE]

Then, create a relationship like below:

ARCUSO - [ColumninARCUSO] ---> CSOPTFD [ColumninCSOPTFD]

Best regards,

Yuliana Gu

[Samantha]

Hi Yuliana

Thanks for your solution. It working now.

[rchiang]

Hi @Samantha,

I was wondering if you have any duplicates in the Optfield and Value in ARCUSO or CSOPTFD?

Just want to understand your data. 

Thanks, 

[Samantha]

Hi Chiang

Yes, I do have duplicate records in the table.

ARCUSO - per customer can tag to many optional fields

CSTOPFD - per optional fields have many different value

Thanks

[parry2k · Accepted solution]

@Samanthayou cannot create relationship on two fields, best way is to add new calculate column in both table by concatenating these two fields  fields and then set the relation on this new calculated columns. hope it is clear.

[Samantha]

Hi Parry

Thanks for your advice. It working now.