Power BI Chart for Project Status

[Anonymous]

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

[v-zhangti]

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.

[Anonymous]

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.

[Anonymous]

@DataInsights @amitchandak  Please help..am struggling with this requirement. A solution can solve 80% of my worries.

[Greg_Deckler]

@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

[Anonymous]

This is based on Planned Start or Planned Finish? Can we set around the logic with %Complete.

[amitchandak]

@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

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee884e

[Anonymous]

Does this help to create the chart?