cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rax99 Member
Member

Comparing two tables to find differences

I have 2 tables that should, in theory, be identical;

 

Table 1

agentIDDateRingtimeTalktimeConnectionIDHoldTime
126/09/201912124CID157890
226/09/20195452CID7824256
326/09/20194111CID8461221
426/09/201900CID484510
526/09/2019714CID692411
626/09/20191659CID2659414

 

Table 2

agentIDDateRingtimeTalktimeConnectionIDHoldTime
126/09/201912124CID157891
226/09/20195452CID7824256
326/09/20194111CID8461221
426/09/201900CID484510
526/09/2019514CID692411
626/09/20191658CID2659414

 

However, in reality, appears minor differences in some of the values. basically for each ConnectionID I'm looking for any differences. So all fields should match for the records (date, agentid, ring, talk, and hold times) otherwise flag up the ConnectionID.

 

From the sample above, ConnectionID'sCID15789, CID69241, and CID26594 are NON-mathches thus, should be flagged (as x or red or whatever in a new column/field) 

 

I have tried using LOOKUP on the ConnectionID, IF statement and even the RELATED function but these do not appear to give what I need. Is there a dax/power m query that works well in this situation? Any pointers would be great.

 

Below is a visual representation of the data model.

 

Abbreviated Data Model Illustration:

 

DM.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Comparing two tables to find differences

Hi @rax99 ,

 

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"
)

 

4.PNG

 


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.

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.

View solution in original post

4 REPLIES 4
Nathaniel_C Super Contributor
Super Contributor

Re: Comparing two tables to find differences

Hi @rax99 ,
Start at the bottom, merge the two tables, I used ID, then expand the second table using the table name as a prefix, except don't need ID, then the if statement which checks all matches and flags. 

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
mtable123.PNG

 

mtable12.PNG

 

mtable1.PNG

 

mtable.PNG

 

hwegman Frequent Visitor
Frequent Visitor

Re: Comparing two tables to find differences

Hello @rax99

 

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:

Comparing Two tables.PNG

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.

 

Community Support Team
Community Support Team

Re: Comparing two tables to find differences

Hi @rax99 ,

 

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"
)

 

4.PNG

 


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.

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.

View solution in original post

Community Support Team
Community Support Team

Re: Comparing two tables to find differences

Hi @rax99 ,


How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

 

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.

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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,963)