Forum Discussion
Find missing data
Hello,
I need a way to compare data in two different data sources to see if an event is missing. For instance, if a child is enrolled in the program, I need to know if they are missing a screening. The common field is the child's id number, the program name, the site name, the class name, and the child's name. I need the missing screenings to show in a table with the child's name and the site. I am including examples of my data tables for reference. What I am needing is to compare the ChildPlus IDs in each table to determine which children have a screening and which do not. Then I need to generate a table to show the child's name and site for those missing screenings.
Can someone please explain how?
Hi, Anonymous ;
You could try to create a new table by dax.
Table = ADDCOLUMNS( EXCEPT(VALUES(TableA[ChildPlus Name]),VALUES(TableB[ChildPlus Name])), "site",CALCULATE(MAX('TableA'[Program name]),FILTER('TableA',[ChildPlus Name]=EARLIER(TableA[ChildPlus Name]))))the final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi Anonymous
It is always helpful to share a sample of your model in order to have an acurate reply to your thread.
All said I think you can add a column in each of the source to indicate 0 if the child is there and 1 for true. and then compare the substraction avec the sum of the 2 columns.
my advice is you share a sample of your Model and give more details on how you want to see the outcome.
Hi, Anonymous ;
You could try to create a new table by dax.
Table = ADDCOLUMNS( EXCEPT(VALUES(TableA[ChildPlus Name]),VALUES(TableB[ChildPlus Name])), "site",CALCULATE(MAX('TableA'[Program name]),FILTER('TableA',[ChildPlus Name]=EARLIER(TableA[ChildPlus Name]))))the final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.I had to make a couple of tweaks to make it work but THANK YOU!!! I got it!!
Missing Screenings = ADDCOLUMNS( EXCEPT (VALUES('All enrollment 22-23'[Name (Last, First)]),VALUES('R8 screenings'[Name (Last, First)])),"SITE NAME",CALCULATE(MAX('All enrollment 22-23'[Site Name]),FILTER('All enrollment 22-23',[Name (Last, First)]=EARLIER('All enrollment 22-23'[Name (Last, First)]))))
I have a similar problem but I need more information in my final table.
If I have a list of standard controls as follows:
and a separate list of businesses, along with their own controls:
then I need a way of identifiying which business have missing controls, and which controls are missing from each business, i.e.:
