topic Re: How to show a chain of dependent tasks in Developer

How to show a chain of dependent tasks

Anonymous — Wed, 12 May 2021 01:08:20 GMT

I’m looking for a way to find a chain of predecessors and successors for every task.
It means I need to find dependent tasks to finally see the impact of slacks in projects.
I've prepared a sample data that shows the Output.
In this table, there is a Predecessors Column that contains a list of task indexes that should be done before the task in each row.
The Successors column is a list of tasks that should be done after the task in each row.
It is possible every predecessor index has its own predecessors in its row. In result we have a chain of related tasks.

One of the problems is, indexes are not unique and for retrieving Id (That is unique) I need to check ProjectId as well. ProjectId of the index = ProjectId of predecessors in their rows.

The question is: for each task that I select in another page shows the chain of related tasks like the output.
I’m not sure whether using DAX is better or M. Also, I have no idea that output should be a list or record or table for each task?
I would appreciate it if you help me and guide me to solve the problem.

Thanks for taking the time in advance.

Id	Index	ProjectId	Predecessors	Successors
101	1	60		2
102	2	60	1,7
103	3	60		8
104	1	22		2,6
105	2	22	1
106	5	22	2
107	6	22
108	6	60
109	7	60	6	8
110	8	60	7

In this table, for Id = 102 that index=2 :
1-check predecessors column: 1 and 7,
2-Check the index column to find a index 1,
3-Check the ProjectId that Should be the same: Index 2 ProjectId = 60, So Project Id for Index = 1 should be 60.
4-Do step 1,2,3,4 for index = 1. here it is blank.
5- Do step 2,3,4 for index = 7
6: Depends on the number of tasks in the list of Predecessors field it should be irritated.

Output :

dm-p

@Greg_Deckler

@Eric_Zhang

Re: How to show a chain of dependent tasks

Anonymous — Wed, 12 May 2021 02:04:49 GMT

Hi Anonymous,

Chain structure seems not suitable for power bi data model table.
In my opinion, I'd like to suggest you convert them to parent-child hierarchy and use hierarchy level instead of the relationship direction.

Parent-child hierarchies – DAX Patterns

Regards,
Xiaoxin Sheng

Re: How to show a chain of dependent tasks

Anonymous — Wed, 12 May 2021 04:21:17 GMT

Thanks for getting back to me. I couldn't use the hierarchy functions. Because there are some tasks in the TaskIndex column which aren't in Predecessors column. So, This problem raises an error.

Re: How to show a chain of dependent tasks

Anonymous — Thu, 13 May 2021 04:27:02 GMT

I tried another way. I was hoping you would have a look.
I've written a Query then using Invoke Custom Function added it to a table but it raises an error.
the query is :
(TaskId as text, ProjectId as text , Predecessors as number) =>
let
Task = {},
PreTask = null,
PreOrder = {},
Output =
if Predecessors <> null then
let
PreTask = Table.SelectRows("Dependency", each Dependency[TaskIndex] = Predecessors and Dependency[ProjectId] = ProjectId),
PreOrder= @Query1(PreTask[TaskId],ProjectId, PreTask[Predecessors]),
Task =List.Combine(PreOrder, TaskId)
in
Task
else
Task = List.Combine(TaskId)
in
Output

The logic of this query is almost correct but I need to change it.
Could you please have a look.

Re: How to show a chain of dependent tasks

Anonymous — Tue, 18 May 2021 08:38:11 GMT

Hi Anonymous,

It is possible to use formal to write a recursion to looping table but it not suitable for your scenario. This table includes records with multiple keys and combined with comma, they were not suitable for your custom functions.

BTW, recursion calculation obviously affects the performance of query table calculation. (e.g. tables with more than a hundred records)
Regards,

Xiaoxin Sheng

Re: How to show a chain of dependent tasks

Anonymous — Wed, 19 May 2021 05:07:21 GMT

Thanks,I think it would be better to use SQL queries.

Re: How to show a chain of dependent tasks

Anonymous — Thu, 20 May 2021 07:41:53 GMT

Hi Anonymous,

Ok, I also think SQL query should be suitable for this analysis, you can also take a look at the below link if helps:

sql server - SQL query for parent-child chain - Stack Overflow

Regards,

Xiaoxin Sheng