Forum Discussion
Filtered tasks based on their predecessor tasks
| Example 1 | ||
| Id | Task | Predecessor |
| ST04270 | Task 01 | ST04270 |
| ST04280 | Task 02 | ST04270 |
| ST04300 | Task 03 | ST04270 |
| ST04310 | Task 04 | ST04280 |
| ST04320 | Task 05 | ST04310 |
| ST04330 | Task 06 | ST04340 |
| ST04340 | Task 07 | ST04320 |
| ST04350 | Task 08 | ST04340 |
| Ejemplo2 | ||
| Id | Task | Predecessor |
| ST04270 | Task 01 | ST04270 |
| ST04280 | Task 02 | ST04270 |
| ST04300 | Task 03 | ST04270 |
| ST04310 | Task 04 | ST04280 |
| ST04320 | Task 05 | ST04310 |
| ST04330 | Task 06 | ST04340 |
| ST04340 | Task 07 | ST04320 |
| ST04350 | Task 08 | ST04340 |
Hello
I'm relatively new to Power BI and need help from this community to achieve the previous result. I am working with a Construction Program and my ultimate goal is to get a list of all the tasks that have been defined as its predecessors. This is to narrow down what task might be affecting my selected task from completion, upstream. I hope to get a list of all the filtered predecessor tasks, after selecting any task.
In example 1 above, if I select Task 08, you must filter task 07, which is its predecessor, Task 05, which is task 07 predecessor, and so on.
I'll really appreciate any ideas I might have to accomplish this.
Thank you!
8 Replies
v-lili6-msftCommunity Support
hi Anonymous
For your case, you could try this way as below:
Step1:
Use Path Function to create a path level column
https://docs.microsoft.com/en-us/dax/parent-and-child-functions-dax
Path level = PATH('Table'[Id],'Table'[Predecessor])Step2:
You need a separate dim task table for slicer.
Step3:
Create a measure as below:
For visual filter = var _pathleve=CALCULATE(SELECTEDVALUE('Table'[Path level]),FILTER(ALL('Table'),'Table'[Task] in VALUES('dim Task'[Task]))) return IF(ISFILTERED('dim Task'[Task]),IF(PATHCONTAINS(_pathleve,SELECTEDVALUE('Table'[Id])),1,0),1)Step4:
Then drag [ID],[Task],[Predecessor] field into a table visual and drag this measure into visual level filter of this table and set its filter is 1.
Result:
and here is sample pbix file, please try it.
Regards,
Lin
Thank you so much v-lili6-msft, this is exactly what I was looking for!
v-lili6-msft , a quick follow up on this > what if a Task has multiple parents? Simplified example -
A Task 01 - B Task 02 - C Task 03 - D Task 04 B D Task 04 C E Task 05 - F Task 06 - G Task 07 D G Task 07 F H Task 08 G I Task 09 H
After a bit of research, it seems Path function and BI, in general, doesn't easily deal with multiple parent hierarchies. A few posts are talking about flattening the table using various hierarchy levels? Any idea how we can do that for my case?Thanks again!
- Let me repeat myself as you might have not notice:
"This is a recursive calculation so you should not use DAX for this as DAX does not support recursion apart from a version called "sideways recursion." You should change your data model using Power Query and then write measures against this new structure. The key to your calculation is A CHANGE OF DATA MODEL."
Best
D
- mahoneypat
Microsoft Employee
Try this measure out with the Task field in a table visual. I put your data in a table called 'Tasks' and wrote this measure that seems to do what you are looking for. The key is passing the selected predecessor IDs to the IDs column with TREATAS(). This should work too if each task had multiple rows with different parent tasks (that's why I used Concatenatex() function).
Predecessors = var selectedtask = SELECTEDVALUE(Tasks[Task])var selectedpredecssors = VALUES(Tasks[Predecessor])var predecessors = CALCULATETABLE(VALUES(Tasks[Task]), All(Tasks), TREATAS(selectedpredecssors, Tasks[Id]))return CONCATENATEX(predecessors, Tasks[Task], "; ")If this works for you, please mark it as the solution. Kudos are also appreciated. Please let me know if not.
Regards,
Pat - This is a recursive calculation so you should not use DAX for this as DAX does not support recursion apart from a version called "sideways recursion." You should change your data model using Power Query and then write measures against this new structure. The key to your calculation is A CHANGE OF DATA MODEL.
Best
D
