how to check if a row exists in another table

[FreemanZ]

hi All,

Supposing I have two tables:

table1:

Key	Status	StatusDate
Ticket1	To Do	8/16/2022
Ticket1	To Do	9/8/2022

table2:

Key	Status	MinDate
Ticket1	To Do	8/16/2022

I would like to add a calculated column in table1: 

if the row in table1 exist in table2, returns "include", otherwise "exclude". like this

Key	Status	StatusDate	Check
Ticket1	To Do	8/16/2022	include
Ticket1	To Do	9/8/2022	exclude

I have this question trying to solve another question posted by MikiMihaela27. 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Compare-two-rows-and-return-the-one-with-the-...

[CNENFRNL · Accepted solution]

[Anonymous]

Hi @FreemanZ 

Try with this solution

Check 2 = IF(RELATED('Table 2'[status date])=('Table 1'[Status date]) &&

RELATED('Table 2'[key])=('Table 1'[key]) &&

RELATED('Table 2'[status]) =('Table 1'[status]),

"Include","Exclude")

Best Regards,
Shreya Mukkawar

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @FreemanZ ,

1. Create a calculated column.

Check = IF(RELATED('Table 2'[status date])=('Table 1'[Status date]),"Include","Exclude")

Best Regards,
Shreya Mukkawar

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[FreemanZ]

thank you. @Anonymous , that is very enlightening, but the comparison needs to be done for all columns for a certain row. 

[NikhilChenna]

Hi @FreemanZ ,

I think you can create a unique key column in both the tables based on concating the ticket and date column, 

After that you can use lookup value function and get the second table unique to the first table unique key based on first table's unique key.

After this you can another calculated column with if condition on the new lookuped column saying if it is blank then exclude else include.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!