Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 [Or...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi Anonymous

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

     

     

  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

Hi Anonymous

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's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous

     

    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" )
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Anonymous

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