How to count number of Ids moved from one status to another in DAX?

[Anonymous]

I have a data set that looks like below. 

Id		Modified Date	Created Date	Status
1		8/4/2017			8/4/2017	Backlog
2		6/10/2021			6/10/2021	Backlog
3		3/5/2018			3/5/2018	Backlog
1		6/5/2020			8/4/2017	Do Next
1		30/5/2021			3/5/2020	Analyze
2		5/4/2022			6/10/2021	Do Next
2		8/8/2022			6/10/2021	Analyze

What I want to achieve is that, I want to count how many Ids moved from Backlog to Do Next status using DAX function. So this time, I have two Ids that moved from Backlog to Do Next (Id1 and Id 2).  I am relatively new to DAX. Can anyone please help me on this?

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description, I can clearly understand what you want to get, you can try my steps:

1. Create a calculated column in the data table:

Is matched =

IF(

    [Status]="Do Next",

    IF(

        "Backlog" in

        SELECTCOLUMNS(FILTER('Table',[Modified Date]<=EARLIER([Modified Date])&&[ID]=EARLIER([ID])),"1",[Status]),1,

        0),0

)

The output is like this:

2. Then create a measure to get the count:

Count = SUMX(ALLSELECTED('Table'),[Is matched])

3. Create a card chart and place the measure like this:

And you can get what you want.

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vera_33]

Hi @Anonymous 

I believe there are other ways. You can create a new column in Power Query or DAX calculated column

Column = IF('Table'[Status]="Backlog" || 'Table'[Status] = "Do Next", 1,0)

Then groupby ID to sum up, filter the sum to 2

Measure = 
VAR T1 = GROUPBY('Table','Table'[Id],"@STATUS",SUMX(CURRENTGROUP(),[Column]))
RETURN
COUNTROWS(FILTER(T1,[@STATUS]=2))