cancel
Showing results for
Did you mean:
Helper I

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

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?

1 ACCEPTED SOLUTION
Community Support

Hi, @Surafel

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:

1. Then create a measure to get the count:
``Count = SUMX(ALLSELECTED('Table'),[Is matched])``
1. Create a card chart and place the measure like this:

And you can get what you want.

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.

2 REPLIES 2
Community Support

Hi, @Surafel

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:

1. Then create a measure to get the count:
``Count = SUMX(ALLSELECTED('Table'),[Is matched])``
1. Create a card chart and place the measure like this:

And you can get what you want.

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.

Solution Sage

Hi @Surafel

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))``````

Announcements