Simple "FILTER" and "IF" on multiple matching rows

[thomasgcoelho]

Hello! I'm trying to create a new column that checks if certain information has a match in another table, but I'm failing because my reference column matches more than once.

I have an "Inspections Table", like this:

Month	Inspection Leader	Inspected Location
September	Albert	Front yard
September	Bernard	Back yard
October	Cromwell	Front yard
October	Bernard	Back yard
December	Cromwell	West yard

And a "Planning Table", like this:

Month	Planned Location	Planned Leader
September	Front yard	Albert
September	Back yard	Cromwell
October	West yard	Cromwell
October	Front yard	Bernard
December	West yard	Dennis
December	Back yard	Bernard

I'm trying to create two columns at the end of the "Inspections Table" like these:

Month	Inspection Leader	Inspected Location	Same Leader as planned for the month?	Same Location as planned for the month?
September	Albert	Front yard	Yes	Yes
September	Bernard	Back yard	No	Yes
October	Cromwell	Front yard	Yes	Yes
October	Bernard	Back yard	Yes	No
December	Cromwell	West yard	No	No

I tried going for a FILTER with an IF in DAX, but I can't get it right. I think I'm having trouble with the fact that months may occur more than once in the "Planning Table".

Thanks in advance!

[thomasgcoelho]

Thank you @jdbuchanan71 ! Now I see where I messed up. Marked your reply as solution.

[jdbuchanan71 · Accepted solution]

@thomasgcoelho 

I can get everything to match up except the December - West Yard I must be missing something.  It is in both your inspections and planning table, just with a different leader so I get a Yes for [Same Location as planned for the month?].  Anyway, here are the calculated columns using LOOKUPVALUE.

Same Leader as planned for the month? = 
IF (
    Inspections[Inspection Leader]
        = LOOKUPVALUE (
            Planning[Planned Leader],
            Planning[Month], Inspections[Month],
            Planning[Planned Leader], Inspections[Inspection Leader]
        ),
    "Yes",
    "No"
)

Same Location as planned for the month? = 
IF (
    Inspections[Inspected Location]
        = LOOKUPVALUE (
            Planning[Planned Location],
            Planning[Month], Inspections[Month],
            Planning[Planned Location],Inspections[Inspected Location]
        ),
    "Yes",
    "No"
)