Filtered tasks based on their predecessor tasks

[arpitprakash]

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!

[v-lili6-msft]

hi  @arpitprakash 

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

[arpitprakash]

@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!

[Anonymous]

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

[arpitprakash]

@Anonymous, Sorry for not responding to you earlier, but due to my limited knowledge in this I am not sure what my data model should look like, be it through Power Query. I found the following post and am trying to understand how to replicate it for my data - 

https://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-powerbi/

If I somehow make different hierarchy levels as columns, I will probably be able to filter them out using DAX. Is this the right approach?

[Anonymous]

Not sure if this flattening is going to work because this technique is normally used when you've got a parent-child hierarchy where an item has only 1 parent, not many. I have not yet seen a model where there would be many parents... but it doesn't mean it's not possible. You have to first try to adjust the known solutions to your case and if they don't work, you have to re-think your model and re-shape it.

Best
D

[arpitprakash]

Thank you so much @v-lili6-msft, this is exactly what I was looking for!

[Anonymous]

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]

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