Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
ddorhout
Frequent Visitor

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?

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi Amitchandak Thanks for your reply! 

 

Great!

One addition to made, then it worked: 

 

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

selimovd
Super User
Super User

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
 

 

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

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.