How to calculate and count not into a field

[MYDATASTORY]

Hi Please advise on how to count lost and gained players using not in. 

Each player can have many contracts (ContractReference)

Each player contracts be on different stages depending on which sports they play.

Below is the player contract status

ContractStatusid              ContractStatus

1                                              Expired

2                                              Cancelled

3                                              Active

4                                              PendingDecesion

5                                              Approvalpending

Below is the dummy data with two players id, but with difference contract references

PlayerID    ContractReference        ContractStatusid  ContractStatusContract Start DateContractEnd Date 

PlayerID	ContractReference	ContractStatusid	ContractStatus	Contract Start Date	ContractEnd Date
100	DummyDataRef1	1	Expired	01/01/1980	01/08/2020
100	DummyDataRef2	2	Cancelled	02/01/1980	01/07/2020
100	DummyDataRef3	1	Expired	03/01/1980	03/05/2020
100	DummyDataRef4	2	Cancelled	04/01/1980	03/04/2020
100	DummyDataRef5	1	Expired	05/01/1980	03/05/2020
100	DummyDataRef6	2	Cancelled	06/01/1980	03/06/2020
100	DummyDataRef7	1	Expired	07/01/1980	03/08/2020
100	DummyDataRef8	2	Cancelled	08/01/1980	03/09/2020
100	DummyDataRef9	1	Expired	09/01/1980	03/09/2020
100	DummyDataRef10	2	Cancelled	10/01/1980	03/09/2020
200	DummyDataRef12	1	Expired	01/01/1980	01/08/1920
200	DummyDataRef13	2	Cancelled	02/01/1980	01/09/1920
200	DummyDataRef14	3	Active	03/01/1980	03/01/2023
200	DummyDataRef15	4	PendingDecesion	04/01/1980	03/01/2023
200	DummyDataRef16	5	Approvalpending	05/01/1980	03/01/2023
200	DummyDataRef17	1	Expired	06/01/1980	03/01/2000
200	DummyDataRef18	2	Cancelled	07/01/1980	03/01/2000
200	DummyDataRef19	3	Active	08/01/1980	03/01/2023
200	DummyDataRef20	4	PendingDecesion	09/01/1980	03/01/2023
200	DummyDataRef21	5	Approvalpending	10/01/1980	03/01/2023

Requirements/help request

Question 1: Count number of players lost

Logic: count these players with ContractStatusid is in 1 and 2 and ContractStatusidNOT IN <>’3,4,5’

We also use the Contract start date and end date as a filter to lost players on a particular month/year

Question 2: Count the number of players gained.

Logic: count these players with ContractStatusid is in ‘3,4,5’ and ContractStatusid NOT IN <>’1,2’ 

We use the contract start date to filter the number of players gained on a particular month/year

Answer

• Number of Gained players 1 Playerid   200
• Number of Lost players      1 playerid   100

[FrankAT]

Hi @MYDATASTORY 

with your sample data and logic - if I understood you well - I get the following solution. It's different to yours answer:

Count number of players gained = 
CALCULATE(COUNTROWS('Table'),'Table'[ContractStatusid] IN {3,4,5})

Count number of players lost = 
CALCULATE(COUNTROWS('Table'),'Table'[ContractStatusid] IN {1,2})

Take a look at the attached pbix file.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[MYDATASTORY]

@FrankAT  Thanks for your help.

Below is the answer I was expecting 

1 Player lost

Player id 100 because their status is in in 1,2 and not in 3,4,5

----------

1 Player gained 

Player id 200 because their status is in in 3,4,5 and not in 1,2

basically they must not appear on the other contract status because one player can lose one contract but still have another one active contract and that why we need on in ( select count playerid from player where contractstatusid NOT in(select contractstatusid where contractstatusid not in (3,4,5)