Forum Discussion

Samantha's avatar
Samantha
Advocate I
8 years ago
Solved

Create relationship between 2 tables (Invoice Header and Invoice Details)

I'm new with PowerBI and I need some enlightment with regards to my issue. I have no issue linking these 2 tables using other BI but not Power BI

 

I have 2 SQL tables - ARIBH (Invoice Header) and ARIBD (Invoice Detail)

 

ARIBH contains many Batch no (CNTBTCH) and each CNTBTCH  will have many entries (CNTITEM)

ARIBD contains the detail and are reference to the ARIBH.

 

Both the tables should be link by  CNTBTCH and CNTITEM

 

But when i try to create a link (even create new) is give me an errors ' You can't create a relationship between these two columns because one of the columns must have unique values.'

 

Any help is much appreciated

 

  • Samantha's avatar
    Samantha
    8 years ago

    Hi

     

    It working.

     

    Thanks so much for your guidance.

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    You will probably need to create a combined key column in both tables using something like CONCATENATE and possibly have a bridge table of the unique combinations.

      • stretcharm's avatar
        stretcharm
        Memorable Member
        8 years ago

        You can either creat the in M (QueryEditor) or in DAX

         

        In M add a Custom Column Like this in each table.

        Number.ToText([CNTBTCH])&":"&Number.ToText([CNTITEM])

        Change the type to text and then you can add a relationship.

         

        In Dax create a calculated column for each table

        InvoiceKey = COMBINEVALUES(":",ARIBH[CNTBTCH],ARIBH[CNTITEM])