Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have project tasks in this format.
P1 is the main task with two sub tasks Summary Task 1 and Summary Task 2. Summary Task 1 in turn has two sub tasks ST-Task 1 and ST-Task 2. I only have two levels of hierarchy, as indicated by "Indentation Level" column - 0, 1 and 2. I am trying to achieve the following result(s) - group the tasks under each parent task and achieve the following result - and show this with some level of visual indentation (indicated in "Spaced Out Task Name" column) as follows:
I got the spacing worked out as a separate column with CONCATENATE but this is not showing up on the visual. Also not able to group the tasks as shown above. Thank you. Here is the pbix that I am using.
PS: I am using table visual. I tried the matrix visual and adjusted 'row headers' and stepped layout, but did not get the desired result.
Solved! Go to Solution.
Hi @ek2112
Looks like the HTML is removing the spaces.
Please try this alternative
New Column = REPT( REPT(UNICHAR(127),2), PATHLENGTH( PATH('Tasks'[Task ID],'Tasks'[ParentTaskID]) ) ) & 'Tasks'[Task Name]
Adding this calculated column allows you to control the sort
Task ID Sort = SUBSTITUTE(PATH('Tasks'[Task ID],'Tasks'[ParentTaskID]),"|",".")
I think I found an easier way using IF, so you just need to add an extra column that indicates if the Account is total,subtotal or detail. and create this column
I think I found an easier way to do it using IF , so you dont have to add two extra columns, just an extra column that indicates if is a total - subtotal - detail
Hi @ek2112
Please try adding the following calculated column to your model
New Column = REPT( REPT(" ",4), PATHLENGTH( PATH('Tasks'[Task ID],'Tasks'[ParentTaskID]) ) ) & 'Tasks'[Task Name]
Thanks for looking into this. I have added the column, groups fine, but I am not able to get the indentation on the visual - table or matrix - not sure what I am doing wrong. Here is the updated pbix
Hi @ek2112
Looks like the HTML is removing the spaces.
Please try this alternative
New Column = REPT( REPT(UNICHAR(127),2), PATHLENGTH( PATH('Tasks'[Task ID],'Tasks'[ParentTaskID]) ) ) & 'Tasks'[Task Name]
@Phil_Seamark - got it! It works fine, but now the hierarchy got messed up. Is there any way that the grouping of child tasks are based on the parent task id regardless of their 'task id'? ST-Task 1 and ST-Task 2 should be under Summary Task 1 as their "ParentTaskID" is 1. Thank you. Link to pbix.
@Phil_Seamark just wanted to clarify that I need to group the child tasks under parent tasks based on parent task ID and display them in the order of parent task ids.
You probably need another column that contains something that can be sorted by.
eg. a list like
1
1.1
1.1.1
1.1.2
1.2
1.2.1
2
2.1
2.1.1
2.1.2
... etc
etc....
Would that be ok to include on your table visual to use to control sorting?
Adding this calculated column allows you to control the sort
Task ID Sort = SUBSTITUTE(PATH('Tasks'[Task ID],'Tasks'[ParentTaskID]),"|",".")
Hello,
How would you go about sorting this column if the values go beyond 10? This is what I get as a result.
1
10
10.1
11
11.1
...
2
2.1
However, what I would like is:
1
2
2.1
...
10
10.1
11
11.1
Thank you!!
@Phil_Seamark - brilliant. This worked like a charm. Thank you. I have to create a new WBS column to get this, but this helps a lot.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
89 | |
83 | |
75 | |
49 |
User | Count |
---|---|
142 | |
140 | |
110 | |
69 | |
55 |