Combining Datasets to figure out Candidates Per Hire

[KojoBeef]

Hello!

I'm pretty basic in my knowledge of PowerBI and I've been tasked with finding out the number of candidates that have interviewed before a position is filled. I have two data sets that I need to pull from, but am just unsure where to start really. I'm starting to learn a bit about DAX, but not sure of all the commands just yet. What I'm looking for though for example is to take Position A and find out how many people interviewed until the Fill Total equals the Order total. I'd need to setup this up for all 3 Order Names/Positions even the ones without complete data.

Any help provided would be greatly appreciated!

Data Set 1					Data Set 2
Name	Position	Contact Date	Offer Accepted		Order Name	Order Total	Fill Total
Person 1	A	5/7/2024			A	2	1
Person 2	B	6/7/2024			B	1
Person 3	C	6/18/2024	8/10/2024		C	3	1
Person 4	A	7/8/2024	8/12/2024
Person 5	C	7/17/1985
Person 6	A	7/18/1985
Person 7	A	7/27/1985	8/20/2024

[Anonymous · Accepted solution]

Hi @KojoBeef 

May I ask if Bmejia's reply solved your problem? If so, please accept his reply as a solution.

Also, please allow me to add my solution and hope it helps you.

In my understanding, the "Fill Total" is determined by the "Offer Accepted" total. I used the following DAX to create the “Fill Total” measure:

Fill Total = 
CALCULATE(
    COUNTROWS('Data Set 1'),
    FILTER(
        'Data Set 1',
        NOT(ISBLANK('Data Set 1'[Offer Accepted])) &&
        'Data Set 1'[Offer Accepted] <= MAX('Data Set 1'[Offer Accepted]) &&
        'Data Set 1'[Offer Accepted] >= MIN('Data Set 1'[Offer Accepted]) &&
        'Data Set 1'[Position] = SELECTEDVALUE('Data Set 2'[Order Name])
    )
)

Then create a measure "Interview Count Before Filled" with the following DAX:

Interview Count Before Filled = 
VAR FillTotal = [Fill Total]
VAR OrderTotal = SELECTEDVALUE('Data Set 2'[Order Total])
VAR LatestOfferDate =  
CALCULATE(
    MAX('Data Set 1'[Offer Accepted]),
    FILTER(
        'Data Set 1',
        NOT(ISBLANK('Data Set 1'[Offer Accepted])) &&
        'Data Set 1'[Position] = SELECTEDVALUE('Data Set 2'[Order Name])
    )
)

RETURN
IF(
    FillTotal < OrderTotal,
    CALCULATE(
        COUNTROWS('Data Set 1'),
        'Data Set 1'[Position] = SELECTEDVALUE('Data Set 2'[Order Name])
    ),
    CALCULATE(
        COUNTROWS('Data Set 1'),
        'Data Set 1'[Position] = SELECTEDVALUE('Data Set 2'[Order Name]) &&
        'Data Set 1'[Contact Date] <= LatestOfferDate
    )
)

Here is my final result:

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

[KojoBeef]

This worked perfectly, thanks! I have one additional issue that's come up though. Data Set 1 and 2 also has a Coloumn named 'Location' in them. Coloumn 'Position' can be name the same between the two of them, but would need to be designated different. For example below.

Data Set 1						Data Set 2
Name	Position	Location	Contact Date	Offer Accepted		Order Name	Order Total	Location	Fill Total
Person 1	A	X	5/7/2024			A	2	X	1
Person 2	B	Y	6/7/2024			B	1	Y
Person 3	C	Z	6/18/2024	8/10/2024		C	3	Z	1
Person 4	A	Z	7/8/2024	8/12/2024
Person 5	C	Z	7/17/1985
Person 6	A	X	7/18/1985
Person 7	A	Z	7/27/1985	8/20/2024

[Anonymous]

Hi @KojoBeef 

The created measures still work. Simply put the “Location” column into your visual.

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

[KojoBeef]

Ah, I got it! I was using the wrong 'Location' coloumn and after I switched the 'Order Total' to Not Summarize it works! Thanks so much for your help!

[Bmejia]

You can create a measure like this not sure if data set 2 would be need it. 

Interview Before Position Filled =

CALCULATE(

    Count(Data_Set1[Name]),  -- counts all the position 

        FILTER(Data_Set1,

            MAX(Data_Set1[Offer Accepted]))) +0-- will filter the data base on the last date offer was accepted.

(+0 to show values with no offers else it will only show A and C which do have offers.  Other option is to right click on matrix and click "Show Items with no data" 


Create a matrix with positions and drop in this measure, 

[KojoBeef]

Thanks so much for this! Unfortunately this doesn't seem to work for what I need it to when there's 'Order Total's greater than 1. Still appreciate the help though!