cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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"
)

 

 

View solution in original post

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

View solution in original post

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

 

 

View solution in original post

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

View solution in original post

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 307 members 3,034 guests
Please welcome our newest community members: