Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Jonathan Lascaux]

?Hi,

I contact you for the first time in this channel to get your support. I think I miss some general concepts.

I have a timesheet table with these info :
– Task ID
– Record Date
– Time recorded
– Parent Task ID

I can have tasks setup this way (a task can only have one parent)
– Task 1
         – Task 2
                – Task 4
         – Task 3

My goal is to display in Power BI, my tasks, with the hierarchies and the time spent per task (I could add a filter per date later on, but this should be easy :)):
– Task 1                       1 hour          
         – Task 2              2 hours
                – Task 4       1.5 hours
         – Task 3               1 hours

Later on I will want to add a total based on the sub-tasks, but this is later :
– Task 1                       1 hour                5.5 hours
         – Task 2              2 hours              3.5 hours
                – Task 4       1.5 hours           1.5 hours
         – Task 3               1 hours             1 hour

I was thinking I can create multiple tables with the parent and child IDs for each level of the hierarchy, but after I cannot add the timesheet table …

Any idea here will be great !

——————————
Jonathan Lascaux

——————————

[Lutz Bendlin]

Keep everything in one table, with a “ID”  and “Parent ID”  column per row, and then use the PATH() DAX functions to get the desired results.

——————————
Lutz
——————————
——————————————-

[Jonathan Lascaux]

Hi,

Thank you, this is working great. Just a question, how do you display the hierarchy after using this function ? Don’t we need to split the column per delimiter first and after display each column in a matrix table ? I guess there is another trick, since to do the split, I would need to run this in Power Query.

Thanks?

——————————
Jonathan Lascaux
——————————
——————————————-

[Lutz Bendlin]

I think you can still do this in DAX, via the PATHITEM() function.  Have you tried that?

——————————
Lutz
——————————
——————————————-