issue regarding relationship beween two tables

[sivakumar8]

suppose i had a two tables like below

table1:                                                                 table2:

customer id  sales  date                                    customer id  quantity  date

cus1              100    1/1/17                                 cus1              2            1/1/17

cus1              200     1/1/17                                cus1             3              1/1/17

cus1              500     2/1/17                                 cus1               5            2/1/17

cus1              2000    3/1/17                                cus1            12            3/1/17

cus2             1000     5/1/17                                cus2           9                5/1/17

cus2             250       4/1/17                                cus2          7                  4/1/17

cus2             2500     7/1/17                                 cus2          8                 7/1/17

when i trying to establish a relationship its throwing an error like u have to make unique values 

please help me how to make relation between these two tables without  disturbing the original data

[Phil_Seamark · Accepted solution]

Hi @sivakumar8

The question is, why do you want to create a relationship?  I'm guessing so you can create visuals using Sales and Quantity.

Power BI will only support 1 to many relationships (or 1 to 1), which is why this isn't working.

However, if you create a Customer table that contains 1 row per customer  eg

CustomerID
-------------
Cus1
Cus2
...

Then you can create a relationship between your two existing tables and this new one.  You can then filter/slice and use the field from this axis in your visuals..

Repeat the same for the date.  A good starting point for dates is to create a new DAX table like this

Dates = CALENDARAUTO()

[opticshrew]

Just a note on the above.

If you have table with many customers you could always duplicate an existing table and remove any duplicates and uneeded columns. This will just speed up the entire process.

Hope it helps.

J