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.
Hi All I need a critical help. I tried alot to create a chart for Project status count based no of activities .I have a dummy data and output expected as below
Project ID | Task Name | Activity Status | Person Assigned | Planned Start | Planned Finish | %Complete |
1 | Design | Complete | Mike | 1-Sep | 2-Sep | 100% |
1 | Test | Complete | Mike | 2-Sep | 3-Sep | 100% |
1 | Devlop | Complete | Brent | 3-Sep | 4-Sep | 100% |
2 | Call | Complete | Brent | 5-Sep | 6-Sep | 100% |
2 | Drive | In Progress | Mike | 7-Sep | 19-Sep | 30% |
3 | Working | In Progress | Mike | 7-Sep | 19-Sep | 10% |
3 | Testing | Not Started | Brent | 19-Sep | 20-Sep | 0% |
4 | Switch | Defered | Brent | 3-Sep | 3-Sep | 0% |
5 | File | Not Started | Brent | 21-Sep | 23-Sep | 0% |
5 | Try | Complete | Brent | 3-Sep | 3-Sep | 100% |
Output as
Project ID | Project Status |
1 | Complete |
2 | In Progress |
3 | In Progress |
4 | Deferred |
5 | In Progress |
Hi, @Anonymous
You can try the following methods.
Column:
Project Status =
Var _MaxComplete=CALCULATE(MAX('Table'[%Complete]),ALLEXCEPT('Table','Table'[Project ID]))
Var _MinComplete=CALCULATE(MIN('Table'[%Complete]),ALLEXCEPT('Table','Table'[Project ID]))
VAR _ResultComplete=IF(_MaxComplete=1,_MinComplete,IF(_MinComplete=0,_MaxComplete))
return
CALCULATE (
MAX ( 'Table'[Activity Status] ),
FILTER (
ALL ( 'Table' ),
[%Complete] = _ResultComplete
&& [Project ID] = EARLIER( 'Table'[Project ID] )
)
)
Result:
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks alot for trying to help. But I have added two more tasks for Project ID5 as below, Where there are 2 Not Started and 1 complete, so still Project 5 to be considered in Progress state. But in the table its reflecting as Not Started.
@DataInsights @amitchandak Please help..am struggling with this requirement. A solution can solve 80% of my worries.
@Anonymous Seems like:
Project Status Measure =
VAR __LastDate = MAX('Table'[AssignedPlanned])
VAR __Status = MAXX(FILTER('Table',[Assigned Planned] = __LastDate),[Activity Status])
RETURN
__Status
This will get you your table. For the graphic, you will have to use a disconnected table of your status values. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563
This is based on Planned Start or Planned Finish? Can we set around the logic with %Complete.
@Anonymous , refer if my blog on the same can help
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0
Does this help to create the chart?
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |