Need to count retirements based on Distinct Values in Name field when the Name is repeated in the data

[rmadursk]

I am working on a KPI for how many retirements have occurred for a list of names.  Basically, the data looks like this:

Name              Retire Date    Job Title

First_Guy        1/1/2023       Engineer
First_Guy        1/1/2023       Architect
Second_Guy  1/1/2023       Project Manager
Second_Guy  1/1/2023       Programming
Third_Guy       2/12/2023    Software Engineer
Third_Guy       2/12/2023     Architect
Third_Guy        2.12.2023     VP
Fourth_Guy     3/1/2023       Director
Fifth_Guy           -                       Sales Engineer
Fifth_Guy           -                       Marketing

I need to count how many of the people are retired.  The table was created from an unpivot where all the people were on one line with all of their job titles on the same line.  We need the data that way for other analysis. I should end up with 4 retirements.  

I could add a table from the source that doesn't unpivot but that seems less elegant than a formula or script. 

I've tried a couple of things based on some of the help from this forum dealing with sets but unsuccessfully.  My pseudocode is:

{set of distinct (Name)}-{set of distinct (Name) with retire date}

Seems simple enough but I can't seem to make the connection.  

I also need to show how many retirements/month but I think I can figure that part out once this is done.

Thanks in advance.

[BrunPierre · Accepted solution]

Hi, this could be one way.

Count(DISTINCT Name) - Count({<[Retire Date]={"*"}>} DISTINCT Name)

[rmadursk]

This appears to work. I'd like to understand what is happening so here is what I see.

Easy part is the Count(Distinct Name) - gives me the number of people in the table

The second half is an aggregation count of the set of Retired Dates that corresponds to the list of Distinct Names.

I had something like this but backwards, trying to key off the names first.  Looks like I need to read up on Count some more.

Thank you BrunPierre.

[BrunPierre]

This {"*"} will select anything but NULL.