Report discrepancies in data flow between multiple systems

[Chris1234]

Hi All,

I need help in reporting discrepancies in the data that is between different systems.

So, for example, I have a source system (Table A) which has information as follows.

Name	Id	Email	Course
Tom	123	Tom@mail.com	CS_1
Mini	124	Mini@mail.com	CS_2
Arya	125	Arya@mail.com	CS_1

An intermediate system (Table B)

Name	Id	Email	Course
Tom	123	Tom@mail.com	CS_1
Mini	124	Mini@mail.com	CS_1
Arya	125	Arya@mail.com	CS_1

And a destination System (Table C)

Name	Id	Email	Course
Tom	123	Tom@mail.com	CS_1
Mini	124	Mini@mail.com	CS_1
Arya	125		CS_1

As you can see that the Course for Mini has flown down incorrectly from Table B and Arya is missing the email in Table C.  I want to be able to report these mismatches in the form of a visual (Pie chart ) which shows red as the mismatch count and when I click on it I can drill down to seeing what are the values that don’t match.

Do you have any suggestions or best practices to report this incorrect data flow?

Thanks,

Chris

[ryan_mayu]

@Chris1234 

My solution can't 100% meet your request.

Use lookup value to get email and course value from TA and TB

email from TB = LOOKUPVALUE('TB'[Email],TB[Name],TC[Name])

Then compare if TC = TB and TC = TA

iscorrect = if(TC[Email]=TC[email from TA]&&TC[Email]=TC[email from TB]&&TC[Course]=TC[course from TA]&&TC[Course]=TC[course from TB],"Correct", "Wrong")

Then create a pie chart.

Legend: iscorrect

Details: name

Values: count of iscorrect

Hope this is helpful.

[lbendlin]

LOOKUPVALUE is a great approach. Another approach is to use TREATAS where you impose the parameters of the desired pattern onto the table to be tested, and the gaps will then be shown (or rather you will see stuff missing) as a result.

[parry2k]

@Chris1234 it can be super easy if you clarify the following points:

- ID column is unique in all these 3 Tables?

- what are we checking for incorrect/mismatch, email only?

- Are we comparing the value of only Table C with Table A and Table B? What happens if there is a mismatch between Table A & Table B or that will never happen? If there will be never a mismatch between Table A & Table B, it means we can compare Table C with any one of Table A & Table B.

[Chris1234]

Hi @parry2k ,

Here are the answers to your questions:

1. Yes ID column is unique in all 3 tables.

2. Nope, mismatch could be in any of the fields email, course, name ect.

3. There is a mismatch between Table A and Table B in this case. 

I basically want to report anything that has a mismatch when data flows between the three tables. It would be great if the able numbers are scaleable.

Hope that answers your question.

Kind Regards,

Chris