Power BI Pattern: Find Start and End Date of Task from Children

The project and task planning management systems have the concept of each task starting and ending with a date. Tasks can be a child of other tasks, and the parent tasks should take the whole range that the child task’s date includes. You need to parse the parent-child hierarchy of tasks (which can be many unknown levels), and then calculate the range of dates for the parent task. This can be done in Power BI using parent-child DAX functions. In this article and video, I will explain the solution.

Video

Sample dataset

The sample dataset I used for this example is a list of tasks, which are in a parent-child relationship with each other using TaskId and ParentTaskId. Each task has StartDate and EndDate. Some of the parent tasks don’t.

The requirement is to find the start date and end date for the tasks based on their children’s minimum start date and maximum end date.

This means, for example, TaskId 1, should follow the minimum start date and maximum end date of all its children. Let’s see how we can do that.

DAX parent-child functions

DAX has a set of powerful functions called parent-child functions. These are particularly helpful in parsing a hierarchy of ItemID and ParentItemID structure. The first of these set of functions you can use is called PATH, which just gest the TaskId and the ParentTaskId;

Path = PATH(WBS[TaskId],WBS[ParentTaskId])

This will give you the entire hierarchy of tasks for each task.

If you want to have each task level in a separate column, It is possible using the PathItem and PathItemReverse functions. You can get an output like below;

I have explained this method in full detail in my article here;

PathContains: To check the entire path

Let’s go back to the original requirement mentioned in this article; finding the minimum start date of all children and the maximum end date of that. In order to achieve this result, you should be able to scan the table to find all tasks which have the current task somewhere in their Path. This can be done using the PathContains function. To give you an understanding of how PathContains function works. here is an example;

The expression below checks to see what tasks have the TaskId of 1 in their path, and returns true for them, for others, it returns false. PathContains is also a very useful function when checking the row-level security on an organizational hierarchy structure. Read more about it here:

So a column that can calculate the start date can be like this:

StartRange = 
var task=WBS[TaskId]
return
CALCULATE(
MIN(WBS[StartDate]),
FILTER(
    ALL(WBS),
    PATHCONTAINS(WBS[Path],task) && WBS[StartDate]<>BLANK())
)

and the column that calculates the end date can be like this:

FinishRange = 
var task=WBS[TaskId]
return
CALCULATE(
MAX(WBS[EndDate]),
FILTER(
    ALL(WBS),
    PATHCONTAINS(WBS[Path],task) && WBS[EndDate]<>BLANK())
)

The expressions above are using PathContains, over the entire table. That is why you see ALL(WBS) in the expression. WBS is the name of the table. For each task, you have to scan the entire table, that is why ALL is used. Because we are writing this expression as a column and not a measure.

The outcome can be like below;

This process will take time especially for tables with many rows. Because the full table scan has to be done in order to find the tasks fitting into each path. The good news, however, is that this is only extending the refresh time of the Power BI dataset. The performance of the report will be instant and fast anyway.

In the example dataset, you can see that the expressions calculated the StartRange and EndRange correctly as the minimum start date and maximum start date of the children.

Summary

Parent-child functions are helpful in any scenarios that you have two columns of ItemId and ParentItemId. One of the very useful functions, when you want to check all children of a parent, is PathContains. In this example, you have seen a use-case scenario of that in a task and project planning system.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply