Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to 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
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
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 |
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
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.
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
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?
Hi @sokatenaj,
Could you post the whole formula you're using, so that I can help further investigate on it?
Regards
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
73 | |
64 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |