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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Abbi
Helper I
Helper I

How to Compare two columns from 2 different tables

Hi ALL,

 

Hope every one is doing well. Need your help in comparing 2 columns from different tables.

Putting down my sample data.

Table 1 :

IDDepedency
1234Client
1234Testing
1234Engg
8125Support
6542Testing
6542Support
6542Reporting
6542Client
9654Support
5420 

 

Table 2:

IDDepedency
1234Client
1234Engg
6542Engg
8125Client
8125Engg
8125Support
9654Client
5420Testing

I have to showup all the ID's where there is a Mismatch in Dependencies ( which are present in Table 2 but not there in Table 1) 

As per the sample data above the ouptput has to be 

IDT1.DependencyT2.Dependency
8125SupportClient
 Engg
 Support
6542TestingEngg
Support 
Reporting 
Client 
9654SupportClient
5420 Testing

Please help.

1 ACCEPTED SOLUTION

@Abbi here you go https://www.dropbox.com/t/x3YgD9Jf7DsM58i6
Untitled.png
Code for Table 1 remains the same. Code for table 2 is updated as follows

T2 Dependancy = 
VAR MissingItems = EXCEPT ( Table2, Table1 )
VAR Result =
IF (
    HASONEVALUE ( Append1[ID] ), 
    CONCATENATEX (
        MissingItems,
        Table2[Depedency],
        "," & UNICHAR(10)
    )
)
RETURN
    Result 


If I solve your problem please consider marking my reply as Accepted. Liks (Kudos) are also appreciated.
Thank you

View solution in original post

8 REPLIES 8
Abbi
Helper I
Helper I

@Greg_Deckler , Please help

Abbi
Helper I
Helper I

Hi @tamerj1  I have same vales in Dependency but T1 Dependency is taking Client,Engg where as T2 Dependency is taking the vale as Engg,Client.

 

Please help.

If any other approach please let me know

@Abbi Please clarify further. 
Do you mean that under T1 Dependancy you need to see the missing items that are there in Table2 and vice versa?

Hi @tamerj1 , sorry for causing confusion.

All the Dependencies in Table2 against each id mst be present in the table1. 

As per my sample data ID 1234 has Client and Engg as dependencies in T2 , as both of them are present in Table 1 so I dont need it to be displayed.

where as ID 6542 , has Engg as dependency in T2 , and T1 doesnt have Engg as depedency against ID 6542 in table 2 so We have to display it 

It is now clear. Will get back to you as soon as I can. 

@Abbi here you go https://www.dropbox.com/t/x3YgD9Jf7DsM58i6
Untitled.png
Code for Table 1 remains the same. Code for table 2 is updated as follows

T2 Dependancy = 
VAR MissingItems = EXCEPT ( Table2, Table1 )
VAR Result =
IF (
    HASONEVALUE ( Append1[ID] ), 
    CONCATENATEX (
        MissingItems,
        Table2[Depedency],
        "," & UNICHAR(10)
    )
)
RETURN
    Result 


If I solve your problem please consider marking my reply as Accepted. Liks (Kudos) are also appreciated.
Thank you

@Abbi Please double check your traget result table. It is completely confusing

IDT1.DependencyT2.Dependency
8125SupportClient
 Engg
 Support
6542TestingEngg
Support 
Reporting 
Client 
9654SupportClient
5420 Testing
tamerj1
Super User
Super User

Hi @Abbi ,
Here is the sample file https://www.dropbox.com/t/TRMjGhgQmkfMLS9m
Many ways to get to that but I prefer the following:
In order to get the complete unique list of all ID's, impot your tables into power query and follow these steps
1.png2.png3.png4.png

Then "Close&Load". Open the Relationship tab and make sure relatiohships are set as follows
5.png

In the visuals tab slect the Matrix visual and drag the ID column from the appended table into the rows.
Use the following code to write your measures (one measure for each table):

T(n) Dependancy = 
IF (
    HASONEVALUE ( Append1[ID] ), 
    CONCATENATEX (
        Table(n),
        Table(n)[Depedency],
        "," & UNICHAR(10)
    )
)

 Your report will look something like this:
6.png

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.