Matching Columns in 2 Tables

[gauravnarchal]

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 ID	Product	Category
2349898729	WW	Y
2449898728	WW	Y
2549898727	WW	O
2649898726	WW	O
2749898725	KK	Y
2849898724	KK	Y
2949898723	KK	Y
3049898722	KK	Y
3149898721	KK	W
3249898720	WW	E
3349898719	WW	E
3449898718	TT	R
3549898717	TT	R
3649898716	TT	R
3749898715	TT	R
3849898714	PP	M
3949898713	PP	M
4049898712	LK	N

Table 2

Product	Category
WW	Y
WW	O
KK	Y
TT	R
PP	M
PP	N

How the result should be displayed

Ref ID	Product	Category	Result
2349898729	WW	Y	Valid
2449898728	WW	Y	Valid
2549898727	WW	O	Valid
2649898726	WW	O	Valid
2749898725	KK	Y	Valid
2849898724	KK	Y	Valid
2949898723	KK	Y	Valid
3049898722	KK	Y	Valid
3149898721	KK	W	Valid
3249898720	WW	E	Valid
3349898719	WW	E	Valid
3449898718	TT	R	Valid
3549898717	TT	R	Valid
3649898716	TT	R	Valid
3749898715	TT	R	Valid
3849898714	PP	M	Valid
3949898713	PP	M	Valid
4049898712	LK	N	Incorrect Value

[AllisonKennedy · Accepted solution]

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

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

[SteveCampbell · Accepted solution]

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" )

[AllisonKennedy · Accepted solution]

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

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