cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## 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

Abhishek

12 REPLIES 12
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Skilled Sharer

@v-shex-msft

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

Community Champion
Hi, Try this calculated column

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

Regards
Zubair

Skilled Sharer

I think this should be greater than 1

N-

Community Champion

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

Regards
Zubair

Helper I

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

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

Regards
Zubair

Helper I

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

Skilled Sharer

@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```

Helper I

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

Skilled Sharer

@abhitiwa

You think you could post some sample data here ?

Nick  -

Solution Specialist

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"

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors