Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
abhitiwa
Helper I
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

 

Someone please help ASAP. Thanks a ton!

Abhishek

12 REPLIES 12
v-shex-msft
Community Support
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])

6.PNG

 

Regards,

Xiaoxin Sheng

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

@v-shex-msft

 

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

 

image.png

Zubair_Muhammad
Community Champion
Community Champion

Hi, Try this calculated column

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

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

 

I think this should be greater than 1

 

N-

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


Regards
Zubair

Please try my custom visuals

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

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

Regards
Zubair

Please try my custom visuals

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

@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

 

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

@abhitiwa

 

You think you could post some sample data here ?

 

Nick  -

drewlewis15
Solution Specialist
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:

2017-12-04_10-55-36.jpg

 

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"

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.