Forum Discussion

sokatenaj's avatar
sokatenaj
Advocate II
9 years ago
Solved

Creating Relationships & Matching - Need some help

Hi Folks,

 

I have two tables. One table is all applicants and one table is filled/hires. I want to create a relationship between the two tables by the "Candidate Name" field, but unfortunately since the applicant table has 10 of the same name applying to different jobs I cannot create a relationship because it is looking for unique values and Power BI  just won't do it.  

 

Additionally on my applicant table I have a field I that says "current status name" with a field that says "Linked". I want to say, if 'Applicant'[Name] that have a status of  'Applicant'[Current status name]="Linked" and that Applicant Name matches the name in 'Filled'[Name] then write MATCH, otherwise "Not Matched". Does this make sense? 

 

Any help would be most welcome and appreciated. I've spent two days on this and just can't get anything to work. It's important to know the other correlation I need is just the NAME field on the hires table because the job IDs don't need to match. Only the name. Please and thank you!

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi sokatenaj,

    Additionally on my applicant table I have a field I that says "current status name" with a field that says "Linked". I want to say, if 'Applicant'[Name] that have a status of  'Applicant'[Current status name]="Linked" and that Applicant Name matches the name in 'Filled'[Name] then write MATCH, otherwise "Not Matched". 

    If I understand you correctly, you should be able to use the formula below to create a new calculate column in this scenario. :smileyhappy:

    Column =
IF (
    Applicant[Current Status Name] = "Linked"
        && CONTAINS (
            Filled,
            Filled[Candidate Identifier], Applicant[Candidate Identifier]
        ),
    "Match",
    "Not Matched"
)

     

    Regards

8 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi sokatenaj,

     

    Could you post your table structures with some sample/mock data, and the expected result against the data? So that we can better assist on this issue? :smileyhappy:

     

    Regards

    • sokatenaj's avatar
      sokatenaj
      Advocate II
      9 years ago

      Hi v-ljerr-msft,

       

      Here you go. As a reminder, I cannot create a relationship because the candidate name and candidat identifier in the applicant table can show up 10 times if they applied to multiple jobs. So I need to think of a different way to validate that there is a "match". Hope this helps. Thanks so much!!!

       

      Applicant Table

      Job IDNameCandidate IdentifierPosting TitleDepartment NumberDepartment NameRecruiter NameHiring Manager NameCurrent Req StatusFirst Fully Approved DateLatest Cancelled DateLatest Filled DateCurrent Step NameCurrent Status Name
      3042478Mouse, Mickey124555Cartoon CharacterCART123DisneyDisney, WaltBlah, BlahApproved6/28/2017  OpenNew
      5448848Duck, Daisy128487Cartoon CharacterCART222ForestDisney, WaltBlah, BlahApproved6/28/2017  OpenAwaiting Response - Email
      6545644Duck, Darkwing585456Disney DudeCART124DisneyDisney, WaltBlah, BlahApproved6/28/2017  OpenLinked
      5998895Chipmunk, Alvin868677Music DudeCART222SevilleBagdasarian, RossHa, BlahApproved6/28/2017  OpenAwaiting Response - Email
      3042478Duck, Darkwing585456Disney DudeCART124DisneyDisney, WaltBlah, BlahApproved6/28/2017  OpenAwaiting Response - Email

       

       

      Filled Table

      Job IDNameCandidate IdentifierPosting TitleRecruiter NameHiring Manager NameCurrent Req StatusFirst Fully Approved DateLatest Cancelled DateLatest Filled DateCurrent Step Name
      1000877Mouse, Mickey124555Cartoon CharacterDisney, WaltBlah, BlahFilled6/28/2017 7/2/2017Offer Accepted
      8878787Duck, Darkwing585456Chipunks BandDisney, WaltBlah, BlahFilled6/28/2017 7/2/2017Offer Accepted
      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi sokatenaj,

        Additionally on my applicant table I have a field I that says "current status name" with a field that says "Linked". I want to say, if 'Applicant'[Name] that have a status of  'Applicant'[Current status name]="Linked" and that Applicant Name matches the name in 'Filled'[Name] then write MATCH, otherwise "Not Matched". 

        If I understand you correctly, you should be able to use the formula below to create a new calculate column in this scenario. :smileyhappy:

        Column =
IF (
    Applicant[Current Status Name] = "Linked"
        && CONTAINS (
            Filled,
            Filled[Candidate Identifier], Applicant[Candidate Identifier]
        ),
    "Match",
    "Not Matched"
)

         

        Regards