Hi all,
I have data about projects, where the projects have hierarchy levels of Project > Stages > Task Groups > Task, and a deadline for each task. I've used a matrix visual and written a measure which, combined with conditional formatting, turns it into a gantt chart. (Cannot use any of the Gantt addins - free ones did not meet my needs and company will not pay for a licence.)
This is working well, but the only issue left is that the matrix is sorting the hierarchy alphabetically. It should sort by the earliest deadline date at each level. Stages, Task Groups and Tasks can have the same name across different projects, so Sort By Column does not work appropriately. And I don't want to append anything to the names, like a number.
Example of what the data looks like is below - this is heavily simplified mock data but hopefully enough to demonstrate the issue. It should sort so in project A0001, Setup is the first stage, then Design, etc. Then within the Design stage, the Task Group should be sorted so Sketching is first, then 3D Model.
Any help appreciated! This is my first post so let me know if any more info is needed. Also happy to use a Power Query based solution rather than DAX is that's more appropriate.
| Project ID | Stage | Task Group | Task | Deadline |
| A0001 | Delivery | Client Review | Task 1 | 21-04-2022 |
| A0001 | Delivery | Client Review | Task 2 | 23-04-2022 |
| A0001 | Delivery | Shipping | Task 1 | 19-04-2022 |
| A0001 | Delivery | Shipping | Task 2 | 20-04-2022 |
| A0001 | Design | 3D Model | Task 1 | 09-04-2022 |
| A0001 | Design | 3D Model | Task 2 | 15-04-2022 |
| A0001 | Design | Sketching | Task 1 | 11-04-2022 |
| A0001 | Design | Sketching | Task 2 | 07-04-2022 |
| A0001 | Processing | 3D Printing | Task 1 | 15-04-2022 |
| A0001 | Processing | 3D Printing | Task 2 | 15-04-2022 |
| A0001 | Processing | Packaging | Task 1 | 16-04-2022 |
| A0001 | Processing | Packaging | Task 2 | 16-04-2022 |
| A0001 | Setup | Resourcing | Task 1 | 10-04-2022 |
| A0001 | Setup | Resourcing | Task 1 | 06-04-2022 |
| A0001 | Setup | Spec | Task 1 | 01-04-2022 |
| A0001 | Setup | Spec | Task 2 | 05-04-2022 |
| B0002 | Delivery | Client Review | Task 1 | 01-05-2022 |
| B0002 | Delivery | Client Review | Task 2 | 03-05-2022 |
| B0002 | Delivery | Shipping | Task 1 | 29-04-2022 |
| B0002 | Delivery | Shipping | Task 2 | 30-04-2022 |
| B0002 | Design | 3D Model | Task 1 | 19-04-2022 |
| B0002 | Design | 3D Model | Task 2 | 25-04-2022 |
| B0002 | Design | Sketching | Task 1 | 21-04-2022 |
| B0002 | Design | Sketching | Task 2 | 17-04-2022 |
| B0002 | Processing | 3D Printing | Task 1 | 25-04-2022 |
| B0002 | Processing | 3D Printing | Task 2 | 25-04-2022 |
| B0002 | Processing | Packaging | Task 1 | 26-04-2022 |
| B0002 | Processing | Packaging | Task 2 | 26-04-2022 |
| B0002 | Setup | Resourcing | Task 1 | 20-04-2022 |
| B0002 | Setup | Resourcing | Task 1 | 16-04-2022 |
| B0002 | Setup | Spec | Task 1 | 11-04-2022 |
| B0002 | Setup | Spec | Task 2 | 15-04-2022 |
