cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

I have project tasks in this format.

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
Employee

Hi @ek2112

Looks like the HTML is removing the spaces.

```New Column =
REPT(
REPT(UNICHAR(127),2),
PATHLENGTH(
)

Proud to be a Datanaut!

Employee

Adding this calculated column allows you to control the sort

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

Proud to be a Datanaut!

11 REPLIES 11
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,
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
Employee

Hi @ek2112

```New Column =
REPT(
REPT(" ",4),
PATHLENGTH(
)

Proud to be a Datanaut!

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

Employee

Hi @ek2112

Looks like the HTML is removing the spaces.

```New Column =
REPT(
REPT(UNICHAR(127),2),
PATHLENGTH(
)

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.

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

Employee

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?

Proud to be a Datanaut!

Employee

Adding this calculated column allows you to control the sort

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

Proud to be a Datanaut!

Frequent Visitor

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors