cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dcg38524
Helper III
Helper III

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 in table below doesn't work and would like to see a solution to this problem.

 

VerifyCol = If(RELATED('Table B'[Value] ) = (Table A[Value]), "Yes", "No"))

Thank you for any input or advice

Don

1 ACCEPTED SOLUTION

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
v-joesh-msft
Solution Sage
Solution Sage

Hi @dcg38524 ,

The RELATED function requires that a relationship exists between the current table and the table with related information. You specify the column that contains the data that you want, and the function follows an existing many-to-one relationship to fetch the value from the specified column in the related table.

In your case, the relationship between the two tables should look like the following picture, Table B on the one side, Table A on the many side:

1.PNG

If it doesn't meet your requirement, kindly share your sample data if you don't have any Confidential Information.

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @dcg38524 ,

In my file the DAX code works fine, you can check if a relationship is created between the two tables.

31.PNG

Because the row contexts and relationships do not interact in any way, so If you want to access columns on the one side of a relationship from the table on the many side of the relationship, you must use the RELATED function.

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EW4RbsTIoa5PptdqPO...

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!