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 is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.