Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
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
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
2 | |
2 | |
1 | |
1 |
User | Count |
---|---|
20 | |
3 | |
2 | |
2 | |
2 |