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, re...
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.

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