cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gauravnarchal
Post Partisan
Post Partisan

Matching Columns in 2 Tables

I have two tables as shown below. I want to validate and match the values from Product column of table 1 with Product column of Table 2 else it should return “Incorrect Value”.

 

Instead of creating a lookup table, can this be done through measure? Pls suggest the best option.

 

Table 1

Ref IDProductCategory
2349898729WWY
2449898728WWY
2549898727WWO
2649898726WWO
2749898725KKY
2849898724KKY
2949898723KKY
3049898722KKY
3149898721KKW
3249898720WWE
3349898719WWE
3449898718TTR
3549898717TTR
3649898716TTR
3749898715TTR
3849898714PPM
3949898713PPM
4049898712LKN

 

Table 2

ProductCategory
WWY
WWO
KKY
TTR
PPM
PPN

 

How the result should be displayed

Ref IDProductCategoryResult
2349898729WWYValid
2449898728WWYValid
2549898727WWOValid
2649898726WWOValid
2749898725KKYValid
2849898724KKYValid
2949898723KKYValid
3049898722KKYValid
3149898721KKWValid
3249898720WWEValid
3349898719WWEValid
3449898718TTRValid
3549898717TTRValid
3649898716TTRValid
3749898715TTRValid
3849898714PPMValid
3949898713PPMValid
4049898712LKNIncorrect Value

 

2 ACCEPTED SOLUTIONS
AllisonKennedy
Super User
Super User

Is there a relationship between the tables? If not, you can use something like:

IF( SELECTEDVALUE(Table1[Product]) IN VALUES(Table2[Product]), "valid", Incorrect")

Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

SteveCampbell
Memorable Member
Memorable Member

You can try

Measure = 
var _sv = RIGHT(SELECTEDVALUE(Table1[Ref ID]),2)
VAR _LU = Calculate( FIRSTNONBLANK(  Table 2[Category],0 ) , Table 2[Product] = _sv )
var _CV = Table1(ProductCategory)

RETURN

IF ( _LU = _SV, "Valid", "Incorrect Value" )





Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



View solution in original post

2 REPLIES 2
SteveCampbell
Memorable Member
Memorable Member

You can try

Measure = 
var _sv = RIGHT(SELECTEDVALUE(Table1[Ref ID]),2)
VAR _LU = Calculate( FIRSTNONBLANK(  Table 2[Category],0 ) , Table 2[Product] = _sv )
var _CV = Table1(ProductCategory)

RETURN

IF ( _LU = _SV, "Valid", "Incorrect Value" )





Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



View solution in original post

AllisonKennedy
Super User
Super User

Is there a relationship between the tables? If not, you can use something like:

IF( SELECTEDVALUE(Table1[Product]) IN VALUES(Table2[Product]), "valid", Incorrect")

Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.