Mark Differences Between 2 Table

[__zhe]

Hi There,

I have 2 tables, Table 1 and Table 2 and i am trying to mark items that unique/not in each other table

 

We can find and list with EXCEPT but how I want to just to mark it down ?

[Zubair_Muhammad]

@__zhe

In above example, Shouldn't John be unqiue..since serial doesn't exist in Table 2?

[__zhe]

@Zubair_Muhammad , السلام عليكم

Yes you're right, John should be unique and marked as "Yes" too 

[Zubair_Muhammad]

@__zhe

Wa alaikumus salam

You can use this calculated table
From the Modelling Tab>>New Table

Calculated Table =
VAR Common =
    INTERSECT ( Table1, Table2 )
VAR NotCommon =
    EXCEPT ( DISTINCT ( UNION ( Table1, Table2 ) ), Common )
RETURN
    UNION (
        ADDCOLUMNS ( Common, "Unique", "No" ),
        ADDCOLUMNS ( NotCommon, "Unique", "Yes" )
    )

[__zhe]

@Zubair_Muhammad I forgotten to mention those table have different structure and number of column . Wondering if Intersect can be based on certain column only, with name conversion 

e.g. INTERSECT(TABLE1,"Serial Number",[Serial Number]),INTERSECT(TABLE2,"Serial Number",[serialNum1XXX)) ... ... ?

[Zubair_Muhammad · Accepted solution]

@__zhe

In that case, you can select the relevant columns first from both tables using "SELECTCOLUMNS" function

Then apply the above procedure

i.e.

Calculated Table =
VAR firstTable =
    SELECTCOLUMNS ( Table1, "ID", [ID], "Serial", [Serial], "Name", [Name] )
VAR SecondTable =
    SELECTCOLUMNS ( Table2, "ID", [ID], "Serial", [Serial], "Name", [Name] )
VAR Common =
    INTERSECT ( firstTable, SecondTable )
VAR NotCommon =
    EXCEPT ( DISTINCT ( UNION ( firstTable, SecondTable ) ), Common )
RETURN
    UNION (
        ADDCOLUMNS ( Common, "Unique", "No" ),
        ADDCOLUMNS ( NotCommon, "Unique", "Yes" )
    )

[Zubair_Muhammad]

@__zhe

Please see the attached sample file as well

[__zhe]

Syukron, @Zubair_Muhammad It almost there ..

But I've 2 more question related to this if you are OK 

Current DAX, both variable that compared but how to find unique only from 1 column e.g. Serial Number from both table

The result now have "null" but unable to get in query mode to remove this null . Unable relate the data by having null 

[Zubair_Muhammad]

@__zhe

Please could you copy paste some data with expected result

If you could copy paste like this....it will save me time in typing

Customer	Date	Amount
Dave	05-01-18	5
Dave	01-09-18	3
Roy	24-02-18	4
Roy	23-08-18	2

[__zhe]

Hi @Zubair_Muhammad , sorry, jugling with other routine just have chance to reply 

So if we look at the table below, unique will only marked when serial is duplicating , despite the ID or Name is same . Kindly need your advise 

Table 1
ID	Serial	Name
A123	XXX	John
A123	YYY	Anna
B123	ZZZ	John
Table 2
ID	Serial	Name
A123	XXX	John
B123	VVV	John
C123	QQQ	Marrie
Expectation
ID	Serial	Name	Unique
A123	XXX	John	No
A123	YYY	Anna	Yes
B123	ZZZ	John	Yes
A123	XXX	John	No
B123	VVV	John	Yes
C123	QQQ	Marrie	Yes

[v-yulgu-msft]

Hi @__zhe,

Please new a calculated table and add a calculated column in it.

Table_3 = UNION(Table_1,Table_2)

Unique = IF(CALCULATE(COUNT(Table_3[Serial ]),ALLEXCEPT(Table_3,Table_3[Serial ]))>1,"No","Yes")

Best regards,

Yuliana Gu

[__zhe]

Hi @v-yulgu-msft table 1 & 2 have different structure, forgotten to mention in the example and also how to nood include null value

[v-yulgu-msft]

Hi @__zhe,

---

---

table 1 & 2 have different structure, forgotten to mention in the example and also how to nood include null value

As Zubair_Muhammad mentioned in previous reply, you can use SELECTCOLUMNS to deal with this problem.

Regards,

Yuliana Gu