Matching two columns from two tables

[ddorhout]

Hi all,

I am facing an issue in which I would like to compare if the values in two columns match in two tables. When in the other table the same combination exists, I would like to have an outcome of 'Yes' (or 'True'). I would like to make an overview of the percentage that is matching in the end, so combining tables could give false insights.

Here I have an example:

Table 1

Column 1	Column 2	Result column
Mechanical	Item1	Yes
Mechanical	Item2	Yes
Mechanical	Item54	No
Electronics	Item2	No
Electronics	Item3	Yes

Table 2

Column X	Column Y
Mechanical	Item1
Mechanical	Item2
Mechanical	Item56
Electronics	Item3
Electronics	Item4

What I did until now was the following:

Match=

IF (

'Table 1' [Column 1] IN DISTINCT ( 'Table 2'[Column X] ),

IF (

'Table 1' [Column 2] IN DISTINCT ( 'Table 2'[Column Y] ),

"Yes",

"No"

),

"No"

)

At first sight it seems to be working, however, some values are false positive; it happens to see matches that are not there. What could be the problem?

[amitchandak · Accepted solution]

@ddorhout , new column in table 1

New column =
var _1 = countx(filter(Table2, Table1[Column 1] = Table2[Column X] && Table1[Column 2] = Table2[Column Y] ) Table2[Column X])
return
if(isblank(_1, "No", "Yes")

[ddorhout]

Hi Amitchandak Thanks for your reply! 

Great!

One addition to made, then it worked: 

if(isblank(_1), "No", "Yes")

[selimovd]

Hey @ddorhout ,

I would summarize by these 2 columns and then do an intersection.

Check if the following approach works:

Matching Measure = 
VAR vIntersectionTable =
INTERSECT (
    SUMMARIZE ( 'Table 1', 'Table 1'[Column 1], 'Table 1'[Column 2] ),
    SUMMARIZE ( 'Table 2', 'Table 2'[Column X], 'Table 2'[Column Y] )
)

RETURN
IF( COUNTROWS( vIntersectionTable ) > 0, "Yes", "No" )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[ddorhout]

Until now it gives only positive values with the code... You have an idea of what could be going wrong?