Reply
Frequent Visitor
Posts: 3
Registered: ‎11-04-2017
Accepted Solution

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

Accepted Solutions
AlB Super Contributor
Super Contributor
Posts: 839
Registered: ‎11-12-2018

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

 

 

View solution in original post

Frequent Visitor
Posts: 3
Registered: ‎11-04-2017

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

View solution in original post


All Replies
AlB Super Contributor
Super Contributor
Posts: 839
Registered: ‎11-12-2018

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

 

 

AlB Super Contributor
Super Contributor
Posts: 839
Registered: ‎11-12-2018

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" )
Frequent Visitor
Posts: 3
Registered: ‎11-04-2017

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

AlB Super Contributor
Super Contributor
Posts: 839
Registered: ‎11-12-2018

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