Lookup a value in other table - DAX

[abhitiwa]

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

[v-shex-msft]

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]

@v-shex-msft

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

[Zubair_Muhammad]

Hi, Try this calculated column

=calculate(countrows(tableA), filter(tableA, tableA[I'd] =tableB[I'd])) >0

[nickchobotar]

@Zubair_Muhammad

I think this should be greater than 1

N-

[Zubair_Muhammad]

Hi @nickchobotar

If @abhitiwa wants to just check if ID exists then it should be >0

Else if he is checking for duplicates then it should be>1

[abhitiwa]

thanks guys for such a quick response. @Zubair_Muhammad your solution doesn't give any error, but it only returns FALSE as the value. However, most of the values are present in Table A. Can you please look?

is_sell = CALCULATE(COUNTROWS(prop),FILTER(prop,prop[user_id]=VALUE(c_users[id]))) > 0

[Zubair_Muhammad]

Hi
If it is a calculated colum, remove VALUES
Simply use
C_users[I'd]

[abhitiwa]

Hi

yes, its a calculated column. And, I have used VALUE (not VALUES) to convert Text (c_users) to number. Not sure what am I missing here. I then tried to change the data type of this field to Number from Text, and then I receive a diff error as below - 

this expression refers to 'sddsaggv-fgjkg-gfgmk' which has an error

[nickchobotar]

@abhitiwa

What happens when you wrap both IDs in VALUE() ?

is_sell =
CALCULATE (
    COUNTROWS ( prop ),
    FILTER ( prop, VALUE (prop[user_id]) = VALUE ( c_users[id] ) )
)
    > 0

[abhitiwa]

@nickchobotar nope, no luck. I only get FALSE as the values

[nickchobotar]

@abhitiwa

You think you could post some sample data here ?

Nick  -

[drewlewis15]

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"