Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have a table which contains a project task hierarchy. Each task has a duration (days)
I would need to calculate the duration of the whole path for each Task.
E.g. for Task with Id "S002" the duration would be 7 (0+7), For Task "S003" the duration would be 11 (0+7+4), and so on.. any help would be appreciated with the DAX formula.
Edit:
Dax Formula for Path is
Path = PATH(Tasks[Id];Tasks[Predecessor])
Sample data as text.
Id;Duration;Predecessor;Path
S001;0;S001;S001
S002;7;S001;S001|S002
S003;4;S002;S001|S002|S003
S004;14;S003;S001|S002|S003|S004
S005;7;S004;S001|S002|S003|S004|S005
S006;7;S004;S001|S002|S003|S004|S006
S007;14;S005;S001|S002|S003|S004|S005|S007
Can you post your data as text? Really helps when trying to recreate and test these things. Probably some way to do it using the parent child functions in DAX.
https://docs.microsoft.com/en-us/dax/parent-and-child-functions-dax
Cool, try this. PBIX is attached.
Total Duration =
VAR __Table =
ADDCOLUMNS(
FILTER(
{
PATHITEM([Path],1),
PATHITEM([Path],2),
PATHITEM([Path],3),
PATHITEM([Path],4),
PATHITEM([Path],5),
PATHITEM([Path],6),
PATHITEM([Path],7),
PATHITEM([Path],8),
PATHITEM([Path],9),
PATHITEM([Path],10)
},
[Value] <> ""
),
"__Duration",LOOKUPVALUE('parentchild'[Duration],parentchild[Id],[Value])
)
RETURN
SUMX(__Table,[__Duration])
Hi, thanks for the quick reply.
The formula seems to be working fine, but the problem here is that my data was only a sample. At the moment, the maximum depth of the path is 25 but there can be even more tasks in a project.
Of course, I can write the formula so that it supports a fixed deoth (e.g. 50), but if it is possible to do the calculation dynamically it would be better. Any suggestions on this?
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |