Can't create a relationship between two columns

[DarylM]

Hi, I am having trouble creating a relationship betwene two tables. 

One table has a unique customer number and name along with customer details like address, phone, etc. 

The other table with sales transactions for products. There are multiple lines for each item a customer purchased. 

I feel the customer number columns should create the relationship as as a one to many, but it does not work. 

Any thoughts?

[KHorseman · Accepted solution]

Somehow you must have ended up with at least one duplicate in the customer table. You can either use the Remove Duplicates option in the query editor, or perhaps you can hunt down the duplicate and fix it at the source. The easiest way to find the duplicate is to create a measure to count the customer ID column, then drop that column and that measure on a table visual, sort by the measure and see which ones come up more than 1.

[edwardrmiles]

is Sold to Number in Customer Setup Report definitely unique?

You can check in the data view. Select the column and it gives you a row count and a distinct values count

[DarylM]

Yeah. There are 10,650 distinct account numbers. These are all the company account records. I am trying to create filter that I can search by zip or city and populate customers what they have purchased. 

The customer setup report is account details and the other individual line by line transactions. 

[edwardrmiles]

@DarylM I presume then there are 10,650 rows too?

[DarylM]

You suggestion worked. I thought I removed all the duplicates, but there were a few. I removed them in the editor. 

Then I create a calculated column by blending the customer number with the zip code. Then the tables linked. Thanks for your help!