Dax NOT IN | How to calculate lost and gained

[MYDATASTORY]

Hi

I would like advice on how to calculate lost and gained players. much appreciated in advance 

Data Modelling

• Contract Status.[Contract Status id]
• Player.[Player Id]

Business rules 

Each Player can have many contracts  which can be on different status i.e expired and pending renew 

Each player can have   more than one contract on different  contract status

Requirement:

Count  Lost Players,

we use the filter  on Contract Start Date and Contract End Date 

we only count these players their contract status = Expired, Canceled  and Not Active(Status id (3,4&5))

we want to exclude  any contract on contract status: Active & Pending approval (status id (1&2))

-----------------------------

Count gained Players

we use the filter  on Contract Start Date to calculate new players based on the Contract Start Date slicer 

we only count these players their contract status =Active & Pending approval (status id (1&2))

Players Contract Table

Player Id	Contract ID	Contract Start Date	Contract End Date	Contract Status	Contract Status id
Player A	100	01/01/1980	1/2/19981	Expired	3
Player A	101	01/01/1980	1/2/19981	Expired	3
Player A	102	01/01/1980	1/2/19981	Expired	3
Player A	103	01/01/1980	1/2/19981	Expired	3
Player A	104	01/01/1980	1/2/19981	Expired	3
Player A	105	01/01/1980	1/2/19981	Expired	3
Player A	106	07/01/2020	01/02/2023	Active	1
Player B	107	08/01/2020	04/04/2018	Expired	3
Player B	108	09/01/2020	04/04/2018	Expired	3
Player B	109	10/01/2020	04/04/2018	Expired	3
Player B	110	11/01/2020	04/04/2018	Expired	3
Player B	111	12/01/2020	04/04/2018	Expired	3
Player B	112	13/01/2020	04/04/2018	Expired	3
Player B	113	14/01/2020	04/04/2018	Expired	3
Player B	114	15/01/2020	04/04/2018	Expired	3
Player C	115	16/01/1999	20/02/1999	Expired	3
Player C	116	16/01/1999	20/02/1999	Expired	3
Player C	117	16/01/1999	20/02/1999	Canceled	5
Player C	118	16/01/1999	20/02/1999	Canceled	5
Player C	119	16/01/1999	20/02/1999	Expired	3
Player C	120	16/01/1999	20/02/1999	Expired	3
Player C	121	16/01/1999	20/02/1999	Expired	3
Player C	122	16/01/1999	20/02/1999	Not Active	4
Player C	123	16/01/1999	20/02/2020	Pending Approval	2
Player C	124	16/01/1999	20/02/2022	Active	1
Player C	125	16/01/1999	20/02/2022	Active	1

Players Names 

Player Name	Player ID
Player A	NameA
Player B	NameB
Player C	NameC

Contract Status

Contract Status	Contract Status id
Expired	3
Active	1
Pending Approval	2
Not Active	4
Canceled	5

[v-lili6-msft]

hi  @MYDATASTORY 

Could you please tell me if your problem has been solved?

If it is, could you please mark the helpful replies as Answered?

if not please share your expected output based on the sample data.

Regards,

Lin

[FrankAT]

Hi @MYDATASTORY 

if I understood you right you can do it like this. See also attached pbix file:

Count Lost Players = 
VAR _Calculation = 
    CALCULATE(
        [Count Players],
        FILTER(
            'Players Contract Table',
            'Players Contract Table'[Contract Status id] IN {3, 4, 5}
        )
    )
RETURN
    IF(_Calculation = BLANK(), "No Lost Player", _Calculation & " Lost Player(s)"
)

Count Gained Players = 
VAR _Calculation = 
    CALCULATE(
        [Count Players],
        FILTER(
            'Players Contract Table',
            'Players Contract Table'[Contract Status id] IN {1, 2}
        )
    )
RETURN
    IF(_Calculation = BLANK(), "No Gained Player", _Calculation & " Gained Player(s)"
)

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