Forum Discussion

__zhe's avatar
__zhe
Frequent Visitor
7 years ago
Solved

Mark Differences Between 2 Table

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's avatar
    Zubair_Muhammad
    7 years ago

    __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" )
    )
__zhe's avatar
__zhe
Frequent Visitor
7 years ago

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's avatar
Zubair_Muhammad
Community Champion
7 years ago

__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
  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    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

  • __zhe's avatar
    __zhe
    Frequent Visitor
    7 years ago

    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   
    IDSerial Name 
    A123XXXJohn 
    A123YYYAnna 
    B123ZZZJohn 
        
    Table 2   
    IDSerial Name 
    A123XXXJohn 
    B123VVVJohn 
    C123QQQMarrie 
        
    Expectation  
    IDSerial NameUnique
    A123XXXJohnNo
    A123YYYAnnaYes
    B123ZZZJohnYes
    A123XXXJohnNo
    B123VVVJohnYes
    C123QQQMarrieYes
  • __zhe's avatar
    __zhe
    Frequent Visitor
    7 years ago

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