Forum Discussion

Samantha's avatar
Samantha
Icon for Advocate I rankAdvocate I
8 years ago
Solved

Creating relationship from 2 tables

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.

Need Help
Show and Tell
  • parry2k's avatar
    parry2k
    8 years ago

    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's avatar
    v-yulgu-msft
    8 years ago

    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

6 Replies

  • rchiang's avatar
    rchiang
    Frequent Visitor
    8 years ago

    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, 

     

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      8 years ago

      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's avatar
        Samantha
        Icon for Advocate I rankAdvocate I
        8 years ago

        Hi Parry

         

        Thanks for your advice. It working now.

    • Samantha's avatar
      Samantha
      Icon for Advocate I rankAdvocate I
      8 years ago

      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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      Samantha
      Icon for Advocate I rankAdvocate I
      8 years ago

      Hi Yuliana

       

      Thanks for your solution. It working now.