Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
Hi, @Anonymous
According to your description, I can clearly understand what you want to get, you can try my steps:
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:
Count = SUMX(ALLSELECTED('Table'),[Is matched])
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.
Hi, @Anonymous
According to your description, I can clearly understand what you want to get, you can try my steps:
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:
Count = SUMX(ALLSELECTED('Table'),[Is matched])
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.
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))
User | Count |
---|---|
94 | |
78 | |
73 | |
64 | |
60 |
User | Count |
---|---|
106 | |
97 | |
76 | |
63 | |
61 |