Forum Discussion
Matching two columns from two tables
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?
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")
4 Replies
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")Hi Amitchandak Thanks for your reply!
Great!
One addition to made, then it worked:
if(isblank(_1), "No", "Yes")
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicUntil now it gives only positive values with the code... You have an idea of what could be going wrong?
