Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to show a chain of dependent tasks

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.

IdIndexProjectIdPredecessorsSuccessors
101160 2
1022601,7 
103360 8
104122 2,6
1052221 
1065222 
107622  
108660  
10976068
1108607 

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 : 

Zara_Farhadi_0-1620777066515.png

 


@dm-p  

@Greg_Deckler

@Eric_Zhang

 

6 REPLIES 6
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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.



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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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.  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors