Find missing data

[Anonymous]

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?

[AndrewPF]

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.: 

[v-yalanwu-msft · Accepted solution]

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.

[Anonymous]

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

[Anonymous]

This gave me a table - but not of the children missing screenings, instead just all of the children in the program. 

[aj1973]

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.

[Anonymous]

I added the tables and some more details about what it is I need. Do you have any other advice?

[aj1973]

my advice is you share a sample of your Model and give more details on how you want to see the outcome.