Forum Discussion
Report discrepancies in data flow between multiple systems
Anonymous
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.
Anonymous 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.
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
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.
