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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
gauravnarchal
Post Prodigy
Post Prodigy

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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

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  



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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!