Forum Discussion

abhitiwa's avatar
abhitiwa
Icon for Helper I rankHelper I
8 years ago

Lookup a value in other table - DAX

I have a table A with duplicate values in one column. I want to run a vlookup in another table B, and want to return 'TRUE' if it exists in table A. eg below- 

 

TABLE A ::

ID       amount

1            10

2            15

1            25

3            38

 

TABLE B ::

1         TRUE

4         FALSE

 

Someone please help ASAP. Thanks a ton!

Abhishek

Need Help

12 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago
    Hi, Try this calculated column

    =calculate(countrows(tableA), filter(tableA, tableA[I'd] =tableB[I'd])) >0
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI abhitiwa,

     

    You can add a calculated column with below formula to check exist item based on other table:

     

    Check = [ID] in VALUES(Records[ID])

     

    Regards,

    Xiaoxin Sheng

    • nickchobotar's avatar
      nickchobotar
      Icon for Skilled Sharer rankSkilled Sharer
      8 years ago

      Anonymous

       

      That's great. Another way is to wrap VALUES() into a CALCULATE() for context transition.
         *** works with relationship only, though

       

  • drewlewis15's avatar
    drewlewis15
    Icon for Solution Specialist rankSolution Specialist
    8 years ago

    One way to do this would be:

    1. Start on Table B within your Query Editor
    2. Choose Merge Queries on the Home tab and select Table A as the table to merge, selecting ID as the relationship
    3. Join Kind = Left Outer
    4. This will return a new column into Table B called "TABLE A"
    5. Add a custom column with this statement: if Table.First([TABLE A]) = null then "FALSE" else "TRUE"
    6. Delete the "TABLE A" column, and your table should now look like this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"ID"},#"TABLE A",{"ID"},"TABLE A",JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Match", each if Table.First([TABLE A]) = null then "FALSE" else "TRUE"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"TABLE A"})
    in
    #"Removed Columns"