Checking Data for Missing or Duplicates Based on Filters

[Anonymous]

Hi,

I have a dataset with hundreds of employees. Each employee has a name, a position and a status for one of 4 tests taken, as shown below:

I want to know if any of the people, somewhere in the excel spreadsheet that is my data source, maybe have a missing value, like if there are only 3 records for that employee because Test B is missing. I also wonder if maybe somewhere in the data there is an employee with 8 records, because maybe they hold 2 positions. Is there a way to filter this to get the information I want?

Thank you

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could get it by creating the tag calculate column with EARLIER Function

https://powerpivotpro.com/2012/03/the-correct-usage-of-earlier/

tag = IF( CALCULATE(COUNTA('Table'[Name]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name])))=4 ,"correct","wrong")

Best Regards,

Lin

[Anonymous]

Hi @Anonymous 

Not sure what you are asking. 

But you can count the number of records for each employee. Better have an ID an not use the names.

Then Calculate this count against a target number. 

Flag employees where the count <> target.

Cheers!
A