Forum Discussion
Manage relationships (unique values needed)
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."
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 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.
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).