DAX - counting last completed task only

Question

I have a table listing tasks per project. Each project has the same number of tasks and they can be either Completed or In Progress. The tasks don't have to be completed in order so it is possible for a task list to look for example like that:

  Project 1 - 1 - Completed
  Project 1 - 2 - Completed
  Project 1 - 3 - In progress
  Project 1 - 4 - Completed
  Project 1 - 5 - In progress
  Project 2 - 1 - Completed
  Project 2 - 2 - In progress
  Project 2 - 3 - In progress
  Project 2 - 4 - In progress
  Project 2 - 5 - Completed
  Project 3 - 1 - Completed
  Project 3 - 2 - In progress
  Project 3 - 3 - In progress
  Project 3 - 4 - In progress
  Project 3 - 5 - Completed

What I am trying to come up with, is a way to show the count of the last completed task. So if I was to chart the above list, it should show as:

 1 - 0
 2 - 0
 3 - 0
 4 - 1
 5 - 2

It's easy enough for me to do it per project, something like

 CALCULATE(
     MAX(task),
     Status = "Completed")

but I cannot work out how to do it overall.

Answer 1

To create a summary table that shows the number of projects that have the final "Completed" task at a particular step, you can use a virtual table inside a measure and filter it by the current task in the filter context. This will identify which projects have a last completed task matching the current task.

Measure:

Count Num Of Proj = 
VAR CurrentTask = MAX('Table'[Task])
VAR _table = SUMMARIZE(
    ALL('Table'),
    'Table'[Project],
    "MaxTaskCompleted", CALCULATE(
        MAX('Table'[Task]),
        'Table'[Status] = "Completed"
    )
)

VAR _filteredTable = FILTER(
    _table,
    [MaxTaskCompleted] = CurrentTask
)
RETURN IF(ISEMPTY(_filteredTable), 0, COUNTROWS(_filteredTable)

• I store the current task in the filter context as a variable 'CurrentTask' to reference it later.
• Afterwards, I summarize the master table by 'Project' and determine each project's last "Completed" task (see screenshot below to get an idea).
  • ALL('Table') will clear all filters and access the unfiltered table. Without this, Power BI will filter the master table by the current task in the filter context.
  • CALCULATE() finds the highest task value with a completed status for a particular project.
• Afterwards, I filter the virtual table by the current task in the filter context
• Finally, I count the rows to determine the total projects (a DISTINCTCOUNT on 'Project' is also possible) (see screenshot below). If the filtered table is empty, I return 0, otherwise the amount of rows.

Virtual table

Final result

Remarks:

• Test this measure using DAX Studio for performance, as it recalculates the virtual table each time the filter context changes. Some optimization is still possible.