Forum Discussion

aashton's avatar
aashton
Helper V
2 years ago

Normalizing a many-to-many

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?

Need Help

4 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    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's avatar
      aashton
      Helper V
      2 years ago

      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.

  • sevenhills's avatar
    sevenhills
    Super User
    2 years ago

    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's avatar
      aashton
      Helper V
      2 years ago

      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.