Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |