Lookup to see if a column contains a specific value, if true then

[obriaincian]

Hi All,

I'm stuck on this one,

I've 2 tables

DF1

ID

11

22

33

44

55

DF2

ID	Task	Date 1	Date 2
11	Act 1		12/02/2022
11	Act 2	15/05/2022	25/05/2022
22	Act 1	05/06/2022	01/08/2022
22	Act 2	04/02/2022	13/02/2022
33	Act 1	1/11/2022
33	Act 2	19/12/2023	24/05/2022
44	Act 2	18/03/2026	02/02/2022
44	Act 2	12/08/2023	17/05/2022
55	Act 1	22/05/2024	09/09/2020

I need to add a column to DF1, the logic for this column is as follows:

Scenario 1 - Where the ID has the Task "ACT 1" use Date 1, If Date 1 is empty Use Date 2.

Scenario 2 - Where the ID has no Task "ACT 1" use Date 2.

Apologies for the table formatting

Thanks,

[v-jianboli-msft · Accepted solution]

Hi @obriaincian ,

Approve with @ryan_mayu .

Since you didn't give the logic for calculating Date2 such as ID 44, I'll tentatively return it as blank(If this is not your logic, please provide more details). Dax is as follows:

Column = 
var _a = SELECTCOLUMNS(FILTER('DF2',[ID]=EARLIER(DF1[ID])),"Task",[Task])
var _b = MAXX(FILTER('DF2',[ID]=EARLIER(DF1[ID])&&[Task]="Act 1"),[Date 1])
var _c = MAXX(FILTER('DF2',[ID]=EARLIER(DF1[ID])&&[Task]="Act 1"),[ Date 2])
return IF("Act 1" in _a &&_b<> BLANK(),_b,_c)

Final output:

Best Regards,

Jianbo Li

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

[obriaincian]

@v-jianboli-msft Yes ID 44 should be blank as there is no ACT 1, thank you

[ryan_mayu]

@obriaincian 

Let's see ID 44, don't have ACT 1 and has two date 2, then what date will you use?