PricingCommunity

Critical Path in Gantt Chart

What is Critical Path?

The Critical Path is the longest chain of dependent tasks in a project that decides the earliest possible finish date. If any task on this path is delayed, the whole project gets delayed too.

How Does It Work?

  • Most project tools (like MS Project, Smartsheet, etc.) calculate the Critical Path by identifying:

    • The task with the latest end date

    • All predecessor tasks linked by dependencies (like Finish-to-Start) that lead to the task

Displaying the Critical Path in a Gantt Chart in Power BI

To visualize the Critical Path in a Gantt chart, you first need to prepare your data using Power Query; once the critical tasks are identified, you can then display the Critical Path using a xViz Gantt chart visual in Power BI.

The Critical Path is closely linked to task dependencies. To learn how task dependencies are visualized in the xViz Gantt Chart, please refer to the documentation on Connectors

Step 1: Create Custom Function in Power Query

  1. Create a new blank query, rename it to GetCriticalPathTasks, and paste the below code:

(TaskTable as table) as list =>
let
    // task with last End Date
    MaxEndTask = Table.Max(TaskTable, "End Date"),
    FinalTask = MaxEndTask[Task Name],

    // Predecessors tasks
    GetAllPredecessors = (targets as list, found as list) =>
        let
            step = Table.SelectRows(TaskTable, each List.Contains(targets, [Connect To]) and not List.Contains(found, [Task Name])),
            newTasks = step[Task Name],
            allFound = List.Union({found, newTasks}),
            next = if List.IsEmpty(newTasks) then allFound else @GetAllPredecessors(newTasks, allFound)
        in
            next,

    // list of tasks on critical path
    Result = List.Distinct(List.Combine({{FinalTask}, GetAllPredecessors({FinalTask}, {})}))
in
    Result

  • Replace "End Date" with the actual column name you’re using for the task end date.

  • Replace "Connect To" with the name of the column that represents the predecessor or linked task.

  • Replace "Task Name" with the column name used for your project task names.

  1. Navigate to the Task Table in Power Query and add a custom column that returns 1 for critical path tasks and 0 for all others using the below code:

if List.Contains(GetCriticalPathTasks(ChangedTypes), [Task Name]) then 1 else 0

Replace "Task Name" with the column name used for your project task names

This will create a new column with a value of 1 for critical path tasks and 0 for all other tasks, which can then be used in the xViz Gantt chart to visually highlight the critical path.

Image 1: Critical Path column

Step 2: Display Critical Path in Gantt Chart

  1. Now, import xViz Gantt Chart from Microsoft AppSource into your report.

If this is your first time working with custom visuals, then here are the steps to import a custom visual from Microsoft AppSource.

  1. Populate the Display Measure Data field using the Critical Path column created in Power Query.

Image 2: Populare Critical Path Column

  1. Now apply conditional formatting using the rule below to visually highlight the Critical Path.

Image 3: Apply Conditional Formatting

For more details on how to apply conditional formatting in xViz Gantt chart, please refer to the documentation on Steps to enable Conditional Formatting

As a result, the Critical Path will be highlighted, as shown in the image below:

Image 4: Critical Path

Here is an example report file:

2MB
xViz Gantt Chart - Critical Path.pbix
PreviousTips & TricksNextFAQs

Last updated

Was this helpful?