Creating Relationships & Matching - Need some help

[sokatenaj]

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!

[v-ljerr-msft]

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? 

Regards

[sokatenaj]

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 ID

Name

Candidate Identifier

Posting Title

Department Number

Department Name

Recruiter Name

Hiring Manager Name

Current Req Status

First Fully Approved Date

Latest Cancelled Date

Latest Filled Date

Current Step Name

Current Status Name

3042478

Mouse, Mickey

124555

Cartoon Character

CART123

Disney

Disney, Walt

Blah, Blah

Approved

6/28/2017

Open

New

5448848

Duck, Daisy

128487

Cartoon Character

CART222

Forest

Disney, Walt

Blah, Blah

Approved

6/28/2017

Open

Awaiting Response - Email

6545644

Duck, Darkwing

585456

Disney Dude

CART124

Disney

Disney, Walt

Blah, Blah

Approved

6/28/2017

Open

Linked

5998895

Chipmunk, Alvin

868677

Music Dude

CART222

Seville

Bagdasarian, Ross

Ha, Blah

Approved

6/28/2017

Open

Awaiting Response - Email

3042478

Duck, Darkwing

585456

Disney Dude

CART124

Disney

Disney, Walt

Blah, Blah

Approved

6/28/2017

Open

Awaiting Response - Email

Filled Table

Job ID	Name	Candidate Identifier	Posting Title	Recruiter Name	Hiring Manager Name	Current Req Status	First Fully Approved Date	Latest Cancelled Date	Latest Filled Date	Current Step Name
1000877	Mouse, Mickey	124555	Cartoon Character	Disney, Walt	Blah, Blah	Filled	6/28/2017		7/2/2017	Offer Accepted
8878787	Duck, Darkwing	585456	Chipunks Band	Disney, Walt	Blah, Blah	Filled	6/28/2017		7/2/2017	Offer Accepted

[v-ljerr-msft · Accepted solution]

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. 

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

Regards

[sokatenaj]

Hi @v-ljerr-msft,

This was exactly what I needed. Thanks so much! 

[sokatenaj]

@v-ljerr-msft,

Is there any way to add a date criteria to this? I tried multiple ways in the logic and kept getting syntax errors. I want to also add to only show up as a match if the "Latest Filled Date" is 6/1/17 or later. 

[v-ljerr-msft]

Hi @sokatenaj,

The formula below should work. 

Column =
IF (
    Applicant[Current Status Name] = "Linked"
        && CONTAINS (
            Filled,
            Filled[Candidate Identifier], Applicant[Candidate Identifier]
        )
        && Applicant[Latest Filled Date] >= DATE ( 2017, 6, 1 ),
    "Match",
    "Not Matched"
)

Regards

[sokatenaj]

Hi @v-ljerr-msft,

Thanks for this. Funny enough that is where I placed it on my own originally but I get the error "A single value for column 'Hired Date' in table 'Taleo Hires' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result." -- why would I need to aggregate the results? 

[v-ljerr-msft]

Hi @sokatenaj,

Could you post the whole formula you're using, so that I can help further investigate on it? 

Regards