Matching Columns in 2 Tables

[gauravnarchal]

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

Table 1 is being updated by the staff manually in the excel whereas Table 2 is the database from where I want the information to be validated.

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	PP	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	Incorrect Value
3249898720	WW	E	Incorrect Value
3349898719	WW	E	Incorrect Value
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	PP	N	Valid

[amitchandak · Accepted solution]

@gauravnarchal , Try the option

new Measure = if(isblank(countx(filter(Table1, Table1[product] = max(Table2[product]) && Table1[Category] = max(Table2[Category])),Table2[Category])) ,"Incorrect Value", "Valid")

new column = if(isblank(countx(filter(Table2, Table2[product] =Table1[product] && Table2[Category] =Table1[Category]),Table2[Category])) ,"Incorrect Value", "Valid")

[Sumanth_23 · Accepted solution]

hi @gauravnarchal, 

I have created the required solution using check at the table level - see below for the steps to create the same 

Note: You can hide the Key column from report view if you do not want users to see the same. 

1. Create key column for Mapping table 

Key = 'Mapping table'[Product] & "-" & 'Mapping table'[Category]

2. Create key column in Data table

Key = 'Data Entry Table'[Product] & "-" & 'Data Entry Table'[Category]

3. Create relationship between the "Key" columns between both tables

4. Create check column in Data table 

Check = if ('Data Entry Table'[Key] = RELATED('Mapping table'[Key]) , "Valid", "Incorrect Value")

5. Create a table visual with the required data

Please mark the post as a solution if my comment helped with solving your issue. Thanks! 

[amitchandak · Accepted solution]

@gauravnarchal , Try the option

new Measure = if(isblank(countx(filter(Table1, Table1[product] = max(Table2[product]) && Table1[Category] = max(Table2[Category])),Table2[Category])) ,"Incorrect Value", "Valid")

new column = if(isblank(countx(filter(Table2, Table2[product] =Table1[product] && Table2[Category] =Table1[Category]),Table2[Category])) ,"Incorrect Value", "Valid")

[gauravnarchal]

Thanks @amitchandak .

Also, can you please let me know how can i compare only the product columns from table 1 & table 2?