topic Re: How to Compare two columns from 2 different tables in DAX Commands and Tips

How to Compare two columns from 2 different tables

Abbi — Tue, 08 Feb 2022 08:36:13 GMT

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 :

ID	Depedency
1234	Client
1234	Testing
1234	Engg
8125	Support
6542	Testing
6542	Support
6542	Reporting
6542	Client
9654	Support
5420

Table 2:

ID	Depedency
1234	Client
1234	Engg
6542	Engg
8125	Client
8125	Engg
8125	Support
9654	Client
5420	Testing

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

ID	T1.Dependency	T2.Dependency
8125	Support	Client
	Engg
	Support
6542	Testing	Engg
Support
Reporting
Client
9654	Support	Client
5420		Testing

Please help.

Re: How to Compare two columns from 2 different tables

tamerj1 — Tue, 08 Feb 2022 09:40:53 GMT

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

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

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:

Re: How to Compare two columns from 2 different tables

Abbi — Tue, 08 Feb 2022 14:51:19 GMT

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

Re: How to Compare two columns from 2 different tables

Abbi — Tue, 08 Feb 2022 15:01:49 GMT

Greg_Deckler , Please help

Re: How to Compare two columns from 2 different tables

tamerj1 — Tue, 08 Feb 2022 16:29:31 GMT

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?

Re: How to Compare two columns from 2 different tables

tamerj1 — Tue, 08 Feb 2022 16:34:08 GMT

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

ID	T1.Dependency	T2.Dependency
8125	Support	Client
	Engg
	Support
6542	Testing	Engg
Support
Reporting
Client
9654	Support	Client
5420		Testing

Re: How to Compare two columns from 2 different tables

Abbi — Wed, 09 Feb 2022 13:24:11 GMT

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

Re: How to Compare two columns from 2 different tables

tamerj1 — Wed, 09 Feb 2022 18:23:09 GMT

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

Re: How to Compare two columns from 2 different tables

tamerj1 — Thu, 10 Feb 2022 01:27:01 GMT

Abbi here you go https://www.dropbox.com/t/x3YgD9Jf7DsM58i6

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