Distinct count with filter condition but exclude if another filter condition is met

[JKen1234]

I want to create a distinct count of catergories if one row meets a filter condition but exclude the entire category if one of the rows within in it meets another filter condition.

I want a distinct count of person number with an appointment leaving date within the calendar period. But not if they have another appointment that was active in that period.

they way the data should work is each appointment has a start and end date but should only have an leaving date if they leave without another active role. However leaving dates have been put in even if another role was active.

Maybe this is soemthing that i Could fix in power query but not sure how I would do that either.

Some key person number here are:

189 while they left one role in october they started another so didnt actually leave.

123 left appointment 1187 in 2008 but had an active bank role 1256 so didnt actually leave

I tried writing something along the lines of the below but dont think im going about this the right way.

=CALCULATE(DISTINCTCOUNT(Appointments[Person Number]),FILTER(Appointments,
Appointments[Leaving Date]>=MIN('Calendar'[Date])&&Appointments[Leaving Date]<=MAX('Calendar'[Date])&&NOT(Appointments[Start Date]<=MAX('Calendar'[Date])&&([End Date]>MAX('Calendar'[Date])||ISBLANK([End Date])))
)
)

Person Number	Appointment Number	Start Date	End Date	Leaving Date	Appointment Type
123	1003	01/02/2001	01/09/2006	01/09/2006	Permament
123	1187	02/01/2007	02/09/2008	02/09/2008	Temporary
123	1250	03/09/2008	31/10/2022	31/10/2022	Bank
123	1256	02/01/2007	31/10/2022	31/10/2022	Bank
123	1527	01/10/2015	30/06/2016	(blank)	Temporary
456	1613	23/03/2020	31/10/2022	31/10/2022	Bank
789	1618	16/12/2020	07/10/2022	07/10/2022	Permament
101	1630	08/01/2021	20/10/2022	20/10/2022	Bank
101	1651	03/08/2022	04/10/2022	(blank)	Temporary
121	1639	25/01/2022	19/10/2022	19/10/2022	Permament
145	163	02/12/2021	04/10/2022	04/10/2022	Bank
145	1646	04/07/2022	04/10/2022	04/10/2022	Temporary
167	1643	19/04/2022	26/10/2022	26/10/2022	Permament
189	1649	21/06/2022	03/10/2022	03/10/2022	Permament
189	1653	04/10/2022	(blank)	(blank)	Bank
201	1644	07/05/2022	27/10/2022	27/10/2022	Bank
223	1652	13/09/2022	13/10/2022	13/10/2022	Permament
245	1659	03/10/2022	10/10/2022	10/10/2022	Permament

Thanks

[Greg_Deckler · Accepted solution]

@JKen1234 Maybe:

Measure 10 = 
    VAR __Date = DATE(2022,10,31)
    VAR __PN = DISTINCT('Table7'[Person Number])
    VAR __Table = FILTER('Table7',[End Date] >= __Date || [End Date] = BLANK())
    VAR __PN1 = DISTINCT(SELECTCOLUMNS(__Table,"Person Number",[Person Number]))
    VAR __Result = COUNTROWS(EXCEPT(__PN, __PN1))
RETURN
    __Result

[JKen1234]

Thank you! This got me there with a couple of changes:

=
VAR __Date = MAX('Calendar'[Date])
VAR __Table1 = FILTER('Table7',
Table7[Leaving Date]>=MIN('Calendar'[Date])&&Table7[Leaving Date]<=MAX('Calendar'[Date]))
VAR __PN = DISTINCT(DISTINCT(SELECTCOLUMNS(__Table1,"Person Number",[Person Number])))
VAR __Table = FILTER('Table7',Table7[Start Date]<=__Date&&([End Date] > __Date || [End Date] = BLANK()))
VAR __PN1 = DISTINCT(SELECTCOLUMNS(__Table,"Person Number",[Person Number]))
VAR __Result = COUNTROWS(EXCEPT(__PN, __PN1))
RETURN
__Result