Forum Discussion

dreaves1's avatar
dreaves1
Helper II
5 years ago
Solved

Create a calculated column/table for Openings Left

Hello everyone,

 

I have two tables. One is a list of our openings and another is a list of our hires. The two tables are totally different but they do have some common columns: company, job ID, recruiter and discpline. The openings table has a column for the number of openings each job has and the hires table as a status column with "Hired" in each field to represent the hires.

 

I'm looking to create a calculated column that will subtract the number of openings from the hires that are  associated with that open job. The hires table is a sharepoint list and new entries will constantly be added whenever someone is hired.

 

For example:

Opening

Microsoft100Recruiter: John DoeEngineeringNumber of Openings: 20

Hires

Microsoft100Recruiter: John DoeEngineeringCandidate: Jane DoeHired

 

Based on this example that calcuated column should say this job has 19 openings remaining.

General Comment
Need Help
Show and Tell
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, dreaves1 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Openings:

     

    Hires:

     

    There is no relationship between two tables. You may try creating a calculated column in 'Openings' table as below. The pbix file is attached in the end.

    Result = 
[Total Openings]-
COUNTROWS(
    FILTER(
        Hires,
        [Company]=EARLIER(Openings[Company])&&
        [Company Name]=EARLIER(Openings[Company Name])&&
        [Discipline]=EARLIER(Openings[Discipline])&&
        [Recruiter]=EARLIER(Openings[Recruiter])&&
        [Req ID]=EARLIER(Openings[Req ID])&&
        [STEM/Business]=EARLIER(Openings[STEM Business])&&
        [Status]="Hired"
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    dreaves1 

    Frist, you should creat Dimension (Lookup) tables with unique values for each of the fields common to both tables and join each of these dimension tables to your fact tables by one-to-many relationships. 
    And take it from there. 

    If you can provide a sample PBIX file or sample datasets (with no confidential info) we can be more specific in the recommendations

    • dreaves1's avatar
      dreaves1
      Helper II
      5 years ago

      Thank you for your help! Without including all the extra columns this is how the table looks.

       

      Openings table

      Hires

       

    • dreaves1's avatar
      dreaves1
      Helper II
      5 years ago

      How do I create a lookup table for the two tables? Let me know if you need more information from me.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi dreaves1 ,

     

    Please share some sample data in text format and the expected output.

    Also, please share your data model.

    Are these 2 separate table ?

     

     

    Regards,

    Harsh Nathani

     

     

    • dreaves1's avatar
      dreaves1
      Helper II
      5 years ago

      Thank you for your help! Yes they are two seperate tables. Without including all the columns this basically how the data is set up in each table. 

       

      Openings

      Hires

       

    • dreaves1's avatar
      dreaves1
      Helper II
      5 years ago

      Please let me know if you need more information

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, dreaves1 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Openings:

     

    Hires:

     

    There is no relationship between two tables. You may try creating a calculated column in 'Openings' table as below. The pbix file is attached in the end.

    Result = 
[Total Openings]-
COUNTROWS(
    FILTER(
        Hires,
        [Company]=EARLIER(Openings[Company])&&
        [Company Name]=EARLIER(Openings[Company Name])&&
        [Discipline]=EARLIER(Openings[Discipline])&&
        [Recruiter]=EARLIER(Openings[Recruiter])&&
        [Req ID]=EARLIER(Openings[Req ID])&&
        [STEM/Business]=EARLIER(Openings[STEM Business])&&
        [Status]="Hired"
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.