Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to Solution.
@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
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
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
ID | T1.Dependency | T2.Dependency |
8125 | Support | Client |
Engg | ||
Support | ||
6542 | Testing | Engg |
Support | ||
Reporting | ||
Client | ||
9654 | Support | Client |
5420 | Testing |
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:
User | Count |
---|---|
21 | |
20 | |
14 | |
10 | |
8 |
User | Count |
---|---|
30 | |
28 | |
13 | |
12 | |
11 |