Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Comparing two tables to find differences

I have 2 tables that should, in theory, be identical;   Table 1 agentID Date Ringtime Talktime ConnectionID HoldTime 1 26/09/2019 12 124 CID15789 0 2 26/09/2019 5 452 CID782...
Need Help
Show and Tell
Tutorial Requests
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can create a calculated column In TableA using following formula to meet your requirement:

     

    IsDifferentFromTableB = 
IF (
    COUNTROWS (
        FILTER (
            'TableB',
            [agentID] = EARLIER ( [agentID] )
                && [Date] = EARLIER ( [Date] )
                && [Ringtime] = EARLIER ( [Ringtime] )
                && [HoldTime] = EARLIER ( [HoldTime] )
                && [ConnectionID] = EARLIER ( [ConnectionID] )
                && [Talktime] = EARLIER ( [Talktime] )
        )
    ) + 0 > 0,
    "NO",
    "YES"
)

     

    Also you could create in TableB:

     

    IsDifferentFromTableA = 
IF (
    COUNTROWS (
        FILTER (
            'TableA',
            [agentID] = EARLIER ( [agentID] )
                && [Date] = EARLIER ( [Date] )
                && [Ringtime] = EARLIER ( [Ringtime] )
                && [HoldTime] = EARLIER ( [HoldTime] )
                && [ConnectionID] = EARLIER ( [ConnectionID] )
                && [Talktime] = EARLIER ( [Talktime] )
        )
    ) + 0 > 0,
    "NO",
    "YES"
)

     

     


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello Anonymous, 

 

Using the following DAX syntax, I created a calculated table that returns the rows from both tables that do not match. I sorted the table by agentID to compare differences, but you could use ConnectionID too. 

 
Input:
NonMatchingTbl = UNION(EXCEPT('Table 1','Table 2'), EXCEPT('Table 2','Table 1'))
 
Output:

You could also remove one of the of the except functions within the DAX equation, if it doesn't matter which column does not match.  If you were to do this, you would only see the rows that did not match from one table.

 

The caveat to this solution is you must have the same number of rows in each table.