Normalizing a many-to-many

[aashton]

I have an Applications table with ID, Date, Name, Candidate ID, etc.  It is linked to a Contracts table with ID, Date, Name Candidate ID, on Candidate ID, many-to-many.  I am trying to resolve this many-to-many relationship.  How can I create a junction table that includes the Applications ID, Contracts ID, and Candidate ID, but only for Application and Contract ID combos where the Candidate ID matches?

[sevenhills]

I think you are missing some info to make it happen.

   CandidateID  << 1..N << Applications

   CandidateID  << 1..N << Contracts

Let us say, CandidateID is the master data table i.e., no duplicates on Candidate and these two tables seems to like two transaction tables: Applications, Contracts.

But where is the info that says a particular "Contract" is part of an "Application" for a given Candidate? do you have that info?

Say, In these scenarios, there is no contract and application combinations data.

Cand100 has App101, App102.

Cand101 has no app data.

Cand103 has App101, App102.

Cand102 has App201 data.

And,

Cand100 has Contract100.

Cand101 has Contract101

Cand103 has Contract100 ... Same contract

If you have the data that a contract and application are related then yes, you can do that easily as

           CandidateID, ContractID, ApplicationID

[aashton]

@sevenhills  Applications links to Contracts on Candidate ID.  So in your example, Candidate 100 and Candidate 103 can't both be linked to Contract 100.

[Daniel29195]

Hello @aashton 

create a table containing the unique candidateID and its related fields into a new table .

you can do it in poewr query or create a calucalted table using dax as follow : 

dim_candidates = 

var datasource_1 = 
allnoblank(app_tbl[candidate_id])

var datasoource_2 = 
allnonblank(contr_tbl[candidate_id]

RETURN 
distinct(
union( 
datasource_1 ),
datasoource_2 
)
)

then link this new table to both tables .

but then, it all depends on what data you to show . 

let me know if this works for you..

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[aashton]

@DAN Yes, that is the way I am doing it.  But I thought I was missing something, because I am having a ton of trouble getting things to work.  The bigger picture is a table of Leads (people), ID, Name, Phone, Etc.  These Leads can have contacted us via one or many methods, for example, through our website, social media, referrals, etc.  Each of these methods is it's own table.  These Leads can then submit one or more applications and be offered one or more contracts.  I am creating a report that seeks to see how valuable/effective these lead types are (referrals, social media, website, etc), and need to display total number of leads each, application and contract counts by each.  It's just been nothing but trouble getting all of this to display.

For example, when trying to display in a table, the Lead ID, Name, phone, Application ID and Date, Contract ID and date.  I get errors saying it doesn't know the relationship between contracts.