Lookup for dates

[gigotomo]

I have 2 tables , Table1and Table2.

I require to check whether Table2[Date] exists in Table1 and in case it does then Table2[type] should be Red or else Green.

[v-robertq-msft · Accepted solution]

Hi, @gigotomo 

According to your requirement, I think you can achieve this using DAX to create a calculated column in the Table 2 like this:

Type =

IF(

    [Date] in SELECTCOLUMNS('Table 1',"1",[Date]),

    "Red","Green")

And you can get what you want, like this:

You can download my test pbix file here

More info about SelectColumns() function in DAX

Best Regards,

Community Support Team _Robert Qin

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

[PC2790]

Hello @gigotomo ,

Create a new column with the following DAX:

Flag = If(NOT(ISBLANK(LOOKUPVALUE(Table1[Date],Table1[Date],Table2[Date]))),"Red","Green")

[amitchandak]

@gigotomo ,

New column in Table 2 =
var _1 = countx(filter(Table1,Table1[Date] =Table2[Date]), Table1[Date])
return
if(isblank(_1), false(), true())