Manage relationships (unique values needed)

[TaylorVA]

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 customers with a 3 month time period (Q3 2015).

Ex:

Customers      

Customer 123

Customer 456

Customer 789

The Sales Orders table includes the Sales Orders for each of those customers

SO#                       Customers      

SO-400                  Customer 123

SO-401                  Customer 456

SO-402                  Customer 789

To illustrate, I have a Sales Order with 3 distinct items, so in the Cash Sales table, it displays the same Sales Order number in three consecutive rows right next to 3 different item numbers.

Ex:

SO#               Item#          Customers            Date of Sale        

SO-401         9900            Customer 456       09/01/2015

SO-401         9921            Customer 456       09/01/2015

SO-401         9931            Customer 456       09/01/2015

I want to create a relationship for the "Customers" column in the Customers table to the same column in the Sales Orders table, which works perfectly. The problem I seem to have is when I try to create a relationship between the SO# column in the Sales Orders table and the Cash Sales table (due to the Cash Sales table having duplicate values for the SO#).

Is there any way to "roll up" the duplicate values in the SO# column so I can create the relationship with unique values?

Thank you in advance to anyone that can help! 🙂

[Sean]

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]

@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]

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]

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

EDIT:

[TaylorVA]

@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.

[Eric_Zhang]

---

@TaylorVA wrote:

@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.

---

@TaylorVA

blank is not quite equal to null. Try

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

[Anonymous]

With the new formula... is [CS#] unique (no dupes)?

I'll just say that in 3 years of doing power pivot... this error has *never* been wrong.  I have always had an unexpected duplicate (blanks being a great gotcha).

[Sean]

The one side has to have every value of the many side! (Many side does not need to have every value of 1 side)

Basically every value in the many table should be able to be matched in the one table.

Seems to me those new numbers you put in place of blanks in the new column would not be matched on the 1 side.

[Anonymous]

---

@Sean wrote:
The one side has to have every value in the many side! (Many side does not need to have every value in 1 side)

Actually, that is not true. Obviously it is better of the 1 side has all the values, but it will let you create the relationship as long as there are no dupes.