Forum Discussion

TaylorVA's avatar
TaylorVA
Frequent Visitor
10 years ago

Manage relationships (unique values needed)

Hello all,   I'm new to Power BI, and I'm trying to manage relationships between 3 tables -- "Customers", "Sales Orders", and "Cash Sales".     The Customers table includes a list of all current...
Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
10 years ago

Do you have duplicate SO# in the Sales Orders Table?

 

What error message do you get when you try to relate the 2 tables?

TaylorVA's avatar
TaylorVA
Frequent Visitor
10 years ago

Sean

No, I only have unique SO#s in the Sales Orders table, but I have duplicate Customers (due to Customers that placed more than one order within the Q3 2015 time frame).

 

Ex:

SO#                  Customers       

SO-101             Customer 123

SO-102             Customer 123

SO-103             Customer 123

 

SO-121             Customer 456

SO-122             Customer 456

 

SO-131             Customer 789

SO-132             Customer 789

 

 

The error message I get is:

"We cannot create a relationship between 'Sales Orders'[SO#] and 'Cash Sales'[SO#]. This could be because there is missing intermediate data to connect the two columns."

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    10 years ago

    What error do you get when you try to relate? Can you post a screenshot of the error message?

     

    Based on the sample you've provided everything should work!

     

    • Sean's avatar
      Sean
      Icon for Community Champion rankCommunity Champion
      10 years ago

      TaylorVA Okay do you have blanks in those columns? That can trigger that message too!

       

      EDIT:

      • TaylorVA's avatar
        TaylorVA
        Frequent Visitor
        10 years ago

        Sean Yes, I did have blanks, so I went to the Query Editor and created a Custom Column (that I named 'SO#2') and used a formula to fill the blank SO#s with the Cash Sale number:

         

        = if [SO#] = null then [CS#] else [SO#]

         

         

        I then tried creating a relationship between Sales Orders [SO#] and Cash Sales [SO#2] to see if the issue was resolved, but I'm still met with the same error message.