Many to Many Relationship

[Anonymous]

Hello,

I am working on a project that will detect past quotes that we give to our customers. The tool uses two datasources, a quote table which contains all quotes (whether we did business with that customer or not) and a table which contains sales information about the quotes. However, the sales table contains only those quotes in which our business has won/made a sale on. 

The issue I am having is the many to many relationship. I want to join the two tables together via the unique quote ID (an 8 character long number).

For example:

Quote Table:      Won/Lost:   Sales Table:

67881934               Lost

67824431               Won        67824431

67845519               Won        67845519

67802298               Lost

There are obviously many more quotes in the quote table than in the sales table. I don't understand why I am getting many-to-many. All I want to be able to do is have a one-to-one or one-to-many relationship between these two tables so that the data will process faster, and so that I get much more details about the quotes - won or lost.

I can provide a sample dataset if need be.

Thank you

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

You can consider to extract 'quote id' from two tables and remove duplicate records. Then you can use this table as bridge to link two tables with both 'many to one' relationship. It should help mapping records more correctly.

Quote Bridge =
DISTINCT (
    UNION (
        VALUES ( Table1[Quote] ),
        VALUES ( Table2[Quote] )
    )
)

Regards,

Xiaoxin Sheng