Forum Discussion
Solved
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 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 |
- Is there a relationship between the tables? If not, you can use something like:
IF( SELECTEDVALUE(Table1[Product]) IN VALUES(Table2[Product]), "valid", Incorrect") 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" )
2 Replies
- Is there a relationship between the tables? If not, you can use something like:
IF( SELECTEDVALUE(Table1[Product]) IN VALUES(Table2[Product]), "valid", Incorrect") 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" )