Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ek2112
Advocate II
Advocate II

Adding indentation and hierarchy to a list of project tasks

I have project tasks in this format. 

image.png

 

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:

 

image.png

 

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. 

2 ACCEPTED SOLUTIONS

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]

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Adding this calculated column allows you to control the sort

 

Task ID Sort = SUBSTITUTE(PATH('Tasks'[Task ID],'Tasks'[ParentTaskID]),"|",".")

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

11 REPLIES 11
alejandram5
New Member

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 

 

New Column =
IF('MS (3)'[Filter] = "Detail",
REPT(" ",9) & 'MS (3)'[Account], IF('MS (3)'[Filter] = "Subtotal",
REPT(" ",6) & 'MS (3)'[Account], 'MS (3)'[Account]))
 
For my dataset Account will be as I said before, total, subtotal or datail, 
table.PNGCapture.PNG
alejandram5
New Member

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

 

New Column =
IF('MS (3)'[Filter] = "Detail",
REPT(" ",9) & 'MS (3)'[Account], IF('MS (3)'[Filter] = "Subtotal",
REPT(" ",6) & 'MS (3)'[Account], 'MS (3)'[Account]))
 
my column account is the name of the account and my  column Filter tolds me if is detail, total or subtotal.
 
I hope it works for you
Phil_Seamark
Employee
Employee

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]

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark

 

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

 

image.png

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]

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

 

image.png

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Adding this calculated column allows you to control the sort

 

Task ID Sort = SUBSTITUTE(PATH('Tasks'[Task ID],'Tasks'[ParentTaskID]),"|",".")

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.