Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Compare value in two tables

Hello,   I am trying to compare a single value in two table columns ie. "Table A[values] to Table B[values]" and if they match place a "Yes" in new column or "No" if no match.  My DAX code listed i...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    Just in case you want to solve this without creating a relationship between the 2 Tables, write this calculated column in Table2

    Column = if(ISBLANK(LOOKUPVALUE(Table1[Records],Table1[Records],Table2[Received])),"No","Yes")

    Hope this helps.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Joey, below is an example of the tables content and along with the results in the (New Column) I would like to achieve.  Honestly, if there is a DAX comparison script which will compare each table column and if the content matches place a "Yes" or "No" in the new column.

 

 

Table A		           Table B	
Records		         Received	          Match (New Column)
UKIM20010437411		UKIM20010211536	           Yes
UKIM20010442352		USIM10017106447	           No
UKIM20010232566		UKIM20009858062	           No
UKIM20010457623		UKIM20009867850	           No
UKIM20010201394 	UKIM20009903817	           No
UKIM20010211536		UKIM20010300727	           Yes
UKIM20010257629		UKIM20009939100	           No
UKIM20010300727		USIM10017308948	           No
UKIM20010396426 	UKIM20010437411	           Yes
UKIM20010411295		UKIM20010442352	           Yes
UKIM20010179221 	UKIM20010232566	           Yes
Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

Just in case you want to solve this without creating a relationship between the 2 Tables, write this calculated column in Table2

Column = if(ISBLANK(LOOKUPVALUE(Table1[Records],Table1[Records],Table2[Received])),"No","Yes")

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you - Dumb Question, what is the benefit of having the relationship between the 2 table in achieving the "Yes" or "No" in the new column?

     

    Your help is Much appreciated

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      That allows you to use a RELATED() function.  that formula is much shorter than mine.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Can you explain what needs to be done if we want to achieve this in a measure instead of column ?

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      This is an old post.  Please share some data, explain the business context and show the expected result.

  • dronik's avatar
    dronik
    Helper I
    1 year ago

    hello Ashish_Mathur 

    I want to use this solution in one of my reports. The question is - how to apply this based on filtering applied in Table2? Lets say I have below data. I want to match Table2 IDs that have same ID that Table1 and Table2 Country is equal to France.

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      1 year ago

      Hi,

      Write this calculated column formula

      Result = and('Table2'[Country]="France",calculate(countrows('Table2'),filter('Table1','table1'[ID]=earlier('Table2'[ID])))>0)