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.
Hello PBI Community!
I am currently working on a data where an application has a milestone, status, and date, wherein each application have its own dates and milestone progress.
Example data:
Application Number | Milestone | Status | Status Date Tagged |
App1 | New Application | New Application | 1-Jan-19 8:40 |
App1 | Under Review | Under Review | 1-Jan-19 8:45 |
App1 | Under Review | Findings | 1-Jan-19 9:15 |
App2 | New Application | New Application | 1-Jan-19 10:00 |
App2 | Under Review | Under Review | 2-Jan-19 7:54 |
App2 | Passed Review | For booking | 3-Jan-19 16:00 |
App2 | Booked | Booked | 3-Jan-19 18:23 |
App3 | New Application | New Application | 3-Jan-19 8:12 |
App4 | New Application | New Application | 4-Jan-19 9:15 |
App4 | Under Review | Under Review | 4-Jan-19 13:11 |
And I want the data in Power BI to be like this:
New Application | 1 |
Under Review | 2 |
-Findings | 1 |
-Under Review | 1 |
Booked | 1 |
What functions should I use to read the latest entry regardless of Application Number? Because currently I'm having this one:
New Application | 4 |
Under Review | 4 |
-Findings | 1 |
-Under Review | 3 |
Passed Review | 1 |
-For booking | 1 |
Booked | 1 |
which captures all entry.
Thank you in advance for your help!
Solved! Go to Solution.
Hi @Anonymous ,
You can add a additional conditions to exclude passed milestone status:
measure = CALCULATE ( COUNTROWS ( VALUES ( Table[Status] ) ), VALUES ( Table[Milestone] ), Table[Milestone] <> "Passed Review" )
Regards,
Xiaoxin Sheng
Hi @Anonymous ,
You can create a matrix visual and use milestone, status as row, write a measure as value, then drill row fields to bottom level and it should display the result you wanted.
measure = CALCULATE ( COUNTROWS ( VALUES ( Table[Status] ) ), VALUES ( Table[Milestone] ) )
Regards,
Xiaoxin Sheng
Hi @v-shex-msft,
I got all the numbers but I got an extra row and count which I shouldn't get (Passed Review - For booking).
Hi @Anonymous ,
You can add a additional conditions to exclude passed milestone status:
measure = CALCULATE ( COUNTROWS ( VALUES ( Table[Status] ) ), VALUES ( Table[Milestone] ), Table[Milestone] <> "Passed Review" )
Regards,
Xiaoxin Sheng
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |