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 sin...
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

sokatenaj's avatar
sokatenaj
Advocate II
9 years ago

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's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi sokatenaj,

 

The formula below should work. :smileyhappy:

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's avatar
    sokatenaj
    Advocate II
    9 years ago

    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's avatar
      v-ljerr-msft
      Microsoft Employee
      9 years ago

      Hi sokatenaj,

       

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

       

      Regards