Check if a value exists in a table given several conditions and given there's no table relationship

[Anonymous]

Hello,

I have three tables (A, B and C) and due to none unique values in the three tables, no relationship has been created in between these tables. Given certain conditions, I have to check if there exists a value in table B which is equal to a value in table C (and return Yes or No in table A depending on the result). In details:

Conditions:

1. Type (Table A) = "To be verified"
2. Start date (Table A) >= End date (Table C) or Start date (Table A) <= End date (Table C)
3. Name of employee (Table B) = Name of employee (Table C)
4. ID (Table A) = ID (Table B)

If the above conditions are met, return Yes else return No.

Thank you

[Anonymous]

Hello @Anonymous @harshnathani 

Indeed I should have shown some data. To make it simple, I have reduced to two tables : A and B. Furthermore, I have written, at the end, the "logic" I want to apply.

Table A shows proposals received by a company for different temporary jobs. Each job has its own reference.

Table A

Candidate	Reference 1.0	Start date	Recruiter	Title of the job
G	Z65-1	28/02/2020	Arthur	BI analyst
B	X14-1	01/03/2020	James	Dev JAVA
W	Z65-1	28/02/2020	Arthur	BI analyst

Table B shows information regarding past temporary jobs, for example: the start and end date, the job's reference and the person employed.

Reference 2.0	Start date	End date	Title of the job	Employee
A47-1	01/01/2020	28/02/2020	Dev JAVA	B
D69-1	01/10/2019	31/04/2020	BI analyst	D

B had a contract which ended on the 28th of February and make a proposal for another contract for the same job starting on the 1st of March. In other words, here we are talking about contract renewals. Depending on certain conditions, I want to be able to say that an employee's contract has been renewed for a job or not.

Conditions to be met in order to tell if the contract is new or concerns a renewal:

1) The start date of the job must be maximum 60 days after the end date of the previous contract

2) Candidate = Employee

3) Title of the job (table A) = Title of the job (table B)

For information :

1) Reference 1.0 is not the same as reference 2.0

2) The date condition is really important

3) No relationship has been able to be created between these two tables that no unique values exist.

[Anonymous]

That's of course doable in DAX... but it's really a job that should be performed in Power Query. Why don't you do it in PQ then?

Best
D

[Anonymous]

Hello @Anonymous,

Thank you for your answer. I'm not really familiar with M/Power Query and I think that there may be some limitations using Power Query instead of DAX for lookup or maybe I'm wrong.

I think that something like this would have worked if there were relationships between the tables :

Condition = IF(

'Table A'[Candidate]= RELATED('Table B';'Table B'[Employee]) &&

LEFT('Table A'[Title of the job];15) = LEFT(RELATED('Table B';'Table B'[Title of the job]);15) &&

'Table A'[Start Date].[Date] >= RELATED(' Table B';'Table B'[End Date].[Date]) - 60);

"Possible prolongation";

"Nouveau AO")

[Anonymous]

In fact, the opposite is true - Power Query is more powerful than DAX with respect to data mashup (and data mashup is what you ask for).

If you give me some good sample sets (text form), I'll create the M script for you. M is the language that Power Query uses.

Best
D

[Anonymous]

Hello @Anonymous, the sample given above isn't enough ? 

Thank you

[Anonymous]

The sample you've given above is a bit small and I don't think it covers most of the issues you could encounter in sets like these. Please supply some representative samples.

Thanks.

Best
D

[harshnathani]

@Anonymous ,

Can you pls share some sample Data in text format.

Regards,

HN

[Greg_Deckler]

@Anonymous You need to use LOOKUPVALUE or MAXX(FILTER(...),...)

If this is not specific enough, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Unfortunately, your answer doesn't help...

[Anonymous]

@Anonymous, you have to show us sample data and what you want to do. Your initial description is not clear enough, unfortunately.

Best
D