cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ahaBI
Frequent Visitor

Assign disposition numbers to task hierarchy

Hi all,

 

I want to use the Microsoft Power BI template for Project for the Web to follow up on Project Management tasks. A lot of handy functionality out of the box, but there are some frustrations as well - the biggest one being that all tasks are listed equally in the report regardless of parent/sub-level, sorted either alphabetically, by start date, or something else. I would rather like users to view the task hierarchy, so that tasks that belong together are shown side by side.

In Project for the Web, sub tasks are neatly indented, and it is possible to show Disposition numbers (picture below) for relationships between parent tasks and sub tasks (up to 10 levels). My issue is that the Disposition number-data does not seem to be available via the same data import from Dataverse (I have checked many tables, but I cannot say I have the full overview - please let me know if I have missed something). 

ahaBI_0-1639441611621.png

 

So, how can this be recreated with the help from parent-child relationships and indexation?

The following data represents my inputs from Project for the Web, my efforts to extract the relationships, as well as my desired outcome. In reality, I have 8 different projects with approximately 20-150 tasks and subtasks to follow up in each of them. 

From Project for the Web (not Project Online) via Dataverse    Calculations    Desired outcome
IndexProject IDTask IDParent Task IDOutline levelTask HierarchyLevel 1Level 2Level 3Level 4Disposition nr
1AT1 1T1T1   A.1
2AT2T12T1|T2T1T2  A.1.1
3AT3T12T1|T3T1T3  A.1.2
4AT4T33T1|T3|T4T1T3T4 A.1.2.1
5AT5T44T1|T3|T4|T5T1T3T4T5A.1.2.1.1
6AT6T44T1|T3|T4|T6T1T3T4T6A.1.2.1.2
7AT7T44T1|T3|T4|T7T1T3T4T7A.1.2.1.3
8AT8 1T8T8   A.2
9AT9T82T8|T9T8T9  A.2.1
1BT10 1T10T10   B.1
2BT11T102T10|T11T10T11  B.1.1
3BT12 1T12T12   B.2


My issue now is to get the numbers for each level right, meaning that all rows indexed 1-7 are A.1.x, rows indexed 3-7 are A.1.2.x, etc. From there, I would combine these to a Disposition number to sort by in report tables. Ideally I would like the Disposition number in the report to be equal to the Disposition number in Project for the Web, but I am not sure if that is possible.

I would really appreciate guidance on how to create the Disposition numbers allowing me to sort such that related tasks can be shown next to each other in the report. 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1639470418312.png

Disposition Number =
VAR _id = 'Table'[Project ID]
VAR _idx = 'Table'[Index]
VAR _length =
    PATHLENGTH ( 'Table'[Path] )
VAR _p = 'Table'[Path]
VAR _tbl =
    FILTER ( 'Table', 'Table'[Project ID] = _id )
RETURN
    _id & "."
        & CONCATENATEX (
            GENERATESERIES ( 1, _length ),
            COUNTROWS (
                FILTER (
                    _tbl,
                    VAR _len =
                        PATHLENGTH ( 'Table'[Path] )
                    VAR _pid =
                        PATHITEM ( _p, [Value] - 1 )
                    RETURN
                        _len = [Value]
                            && 'Table'[Index] <= _idx
                            && IF ( [Value] = 1, 1, 'Table'[Parent Task ID] = _pid )
                )
            ),
            "."
        )

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1639470418312.png

Disposition Number =
VAR _id = 'Table'[Project ID]
VAR _idx = 'Table'[Index]
VAR _length =
    PATHLENGTH ( 'Table'[Path] )
VAR _p = 'Table'[Path]
VAR _tbl =
    FILTER ( 'Table', 'Table'[Project ID] = _id )
RETURN
    _id & "."
        & CONCATENATEX (
            GENERATESERIES ( 1, _length ),
            COUNTROWS (
                FILTER (
                    _tbl,
                    VAR _len =
                        PATHLENGTH ( 'Table'[Path] )
                    VAR _pid =
                        PATHITEM ( _p, [Value] - 1 )
                    RETURN
                        _len = [Value]
                            && 'Table'[Index] <= _idx
                            && IF ( [Value] = 1, 1, 'Table'[Parent Task ID] = _pid )
                )
            ),
            "."
        )

Thank you so much, @wdx223_Daniel

This is just what I was looking for. It works perfect!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors