cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Stadeo Frequent Visitor
Frequent Visitor

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

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 NameTraining TitleNew Column   
ORG CDSCourse BYes   
ORG ABCCourse CNo   
ORG ATFCourse BYes   
ORG RSDCourse GNo   
Org ABCourse CYes   
      
Table B
 Org Name 1 Org Name 2 Org Name 3 Org Name 4Training Title Group 1Training Title Group 2
Org ABORG CDSOrg ABORG ATFCourse ACourse B
Org ABORG ATFORG ATFORG RSDCourse BCourse C
Org ABORG RSDORG RSDOrg ABCourse CCourse B
Org ABORG ATFOrg ABORG RSDCourse BCourse A
Org ABORG ATFORG ATFORG ATFCourse ACourse C
2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

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

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"
)

 

 

Highlighted
Stadeo Frequent Visitor
Frequent Visitor

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

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

4 REPLIES 4
Super User
Super User

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

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"
)

 

 

Super User
Super User

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

@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" )
Highlighted
Stadeo Frequent Visitor
Frequent Visitor

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

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

Super User
Super User

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

@Stadeo

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