cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Surafel
Helper I
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
v-robertq-msft
Community Support
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:

v-robertq-msft_0-1616139717406.png

 

  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:

v-robertq-msft_1-1616139717412.png

 

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.

View solution in original post

2 REPLIES 2
v-robertq-msft
Community Support
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:

v-robertq-msft_0-1616139717406.png

 

  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:

v-robertq-msft_1-1616139717412.png

 

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.

View solution in original post

Vera_33
Solution Sage
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))

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors