Search / Lookup value from table A in multiple columns in table B

[Stadeo]

I need to write a DAX forumula that will create a New Column in Table A based on the follow criteria:
IF Table A [Org Name] is found in either Table B [Org Name 1], Table B [Org Name 2], Table B [Org Name 3] or Table B [Org Name 4]
AND
If Table A [Training Title] is found in either Table B [Training Title Group 1] or Table B [Training Title Group 2]
I completed New Column based on the IF and AND statements and represents the desired outcome.

(Please note if table joins are necessary)    Thanks so much in advance

Table A
Org Name	Training Title	New Column
ORG CDS	Course B	Yes
ORG ABC	Course C	No
ORG ATF	Course B	Yes
ORG RSD	Course G	No
Org AB	Course C	Yes
Table B
Org Name 1	Org Name 2	Org Name 3	Org Name 4	Training Title Group 1	Training Title Group 2
Org AB	ORG CDS	Org AB	ORG ATF	Course A	Course B
Org AB	ORG ATF	ORG ATF	ORG RSD	Course B	Course C
Org AB	ORG RSD	ORG RSD	Org AB	Course C	Course B
Org AB	ORG ATF	Org AB	ORG RSD	Course B	Course A
Org AB	ORG ATF	ORG ATF	ORG ATF	Course A	Course C

[AlB · Accepted solution]

Hi @Stadeo

How about this:

Column_Test = 
IF (
     (TableA[Org Name] IN VALUES ( TableB[Org Name 1] )
            || TableA[Org Name] IN VALUES ( TableB[ Org Name 2] )
            || TableA[Org Name] IN VALUES ( TableB[ Org Name 3] )
            || TableA[Org Name] IN VALUES ( TableB[ Org Name 4] ))
     
     && (TableA[Training Title] IN VALUES ( TableB[Training Title Group 1] )
                || TableA[Training Title] IN VALUES ( TableB[Training Title Group 2] ));
    "Yes";
    "No"
)

[AlB · Accepted solution]

Hi @Stadeo

How about this:

Column_Test = 
IF (
     (TableA[Org Name] IN VALUES ( TableB[Org Name 1] )
            || TableA[Org Name] IN VALUES ( TableB[ Org Name 2] )
            || TableA[Org Name] IN VALUES ( TableB[ Org Name 3] )
            || TableA[Org Name] IN VALUES ( TableB[ Org Name 4] ))
     
     && (TableA[Training Title] IN VALUES ( TableB[Training Title Group 1] )
                || TableA[Training Title] IN VALUES ( TableB[Training Title Group 2] ));
    "Yes";
    "No"
)

[Stadeo · Accepted solution]

Thank you so much.  This worked.  Awesome!!!!  I spend quite some time on this and I really wasn't getting anywhere.  What a great way to end a Friday.

Steve

[AlB]

@Stadeo

Cool. Glad it helped. How about some kudos then, maybe? 

[AlB]

@Stadeo

Or another version, perhaps more readable:

New_Column_v2 =
VAR _Condition1 =
    TableA[Org Name]
        IN UNION (
            VALUES ( TableB[Org Name 1] );
            VALUES ( TableB[ Org Name 2] );
            VALUES ( TableB[ Org Name 3] );
            VALUES ( TableB[ Org Name 4] )
        )
VAR _Condition2 =
    TableA[Training Title]
        IN UNION (
            VALUES ( TableB[Training Title Group 1] );
            VALUES ( TableB[Training Title Group 1] )
        )
RETURN
    IF ( _Condition1 && _Condition2; "Yes"; "No" )