Need help to get a value from different table based on condition

[Anonymous]

Hi All,

I have two tables like below

Recruitment Table:

Recruitment ID	Department	Experience Type	Recruitment Created Date	Recruitment Close Date	No Of opening
R1	A	Experienced	5/5/2018	5/20/2018	5
R2	B	Experienced	5/10/2018	5/30/2018	4
R3	C	Fresher	5/5/2018	5/20/2018	3
R4	C	Fresher	5/10/2018	5/30/2018	2
R5	C	Fresher	5/1/2018	5/25/2018	4
R6	A	Experienced	5/5/2017	5/20/2017	5
R7	B	Fresher	5/10/2017	5/30/2017	4

Registration Table:

Candidate ID	Department	Experience Type	Registered Date	Interview Result
C1	A	Experienced	5/15/2018	Selected
C2	B	Experienced	5/15/2018	Hold
C3	A	Experienced	4/4/2018	Selected
C4	B	Experienced	4/4/2018	Selected
C5	A	Experienced	5/10/2018	Selected
C6	B	Experienced	5/25/2018	Rejected
C7	C	Fresher	5/15/2018	Selected
C8	C	Fresher	5/4/2018	Rejected
C9	C	Fresher	5/25/2018	Selected
C10	C	Fresher	5/28/2018	Rejected
C11	D	Fresher	5/28/2018	Rejected
C11	E	Fresher	5/28/2018	Rejected

Recuirment: 

1) I want to add Recruitment ID in Registration Table based on the below condition 

• Registration Table. Department = Recruitment Table.Department 
• Registration Table. Experience Type= Recruitment Table.Experience Type
• Registration Table. Registered Date >= Recruitment Table.Recruitment Created Date & Registration Table. Registered Date<= Recruitment Close Date

2) when I select 5/25/2018 in Date filter, it wants to show active Recruitment ID details (selected Date >= Recruitment Table.Recruitment Created Date & selected Date<= Recruitment Close Date)

[v-yulgu-msft · Accepted solution]

Hi @Anonymous，

Create a calculated table which combines Recruitment Table and Registration Table and create a date dimention table. Make sure the combined table is unrelated with date dimention table.

Combined Table =
FILTER (
    CROSSJOIN (
        SELECTCOLUMNS (
            Registration,
            "Candidate ID", Registration[Candidate ID],
            "Department2", Registration[Department],
            "Experience Type2", Registration[Experience Type],
            "Registered Date", Registration[Registered Date],
            "Interview Result", Registration[Interview Result]
        ),
        Recruitment
    ),
    [Department2] = [Department]
        && [Experience Type] = [Experience Type2]
        && [Registered Date] >= Recruitment[Recruitment Created Date]
        && [Registered Date] <= [Recruitment Close Date]
)

DateDim = CALENDAR(MIN(Recruitment[Recruitment Created Date]),MAX(Recruitment[Recruitment Close Date]))

Add corresponding fields into table visual. Add below measure into visual level filter and set its value to 1.

filter =
IF (
    SELECTEDVALUE ( DateDim[Date] )
        >= SELECTEDVALUE ( 'Combined Table'[Recruitment Created Date] )
        && SELECTEDVALUE ( DateDim[Date] )
            <= SELECTEDVALUE ( 'Combined Table'[Recruitment Close Date] ),
    1,
    0
)

Please refer to the uploaded .pbix file for detailed steps.

Best regards,

Yuliana Gu