Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Thanks in advance for your support and help. I have been at this for 2 days with no luck
I have one table called "Task" - the task table contains multiple projects. The data source is combined to include multiple projects. Each project has task outline numbers, Parent task name and outline levels (not sure that this is relevent).
Task Table
Mutliple projects (Project A, ProjectB, and Project C)
-Each project (A, B and C) has outline numbers associated with different task
-Each of the task has a parent task
-Each ParentTask has an outline number associated with it
Project A | Project B | Project C |
0 | 0 | 0 |
1 ProjectA[SummaryTask 1] | 1 ProjectB[SummaryTask 1] | 1 ProjectC[SummaryTask 1] |
1.1 ProjectA[Task a] | 1.1 ProjectB[Task a] | 1.1 ProjectC[Task a] |
1.1.1 ProjectA[Task b] | 1.2 ProjectB[Task b] | 1.2 ProjectC[Task b] |
1.2 ProjectA[Task c] | 1.2.1 ProjectB[Task c] | 1.2.1 ProjectC[Task c] |
1.2.1ProjectA[Task d] | 1.3 ProjectB[Task d] | 1.2.2 ProjectC[Task d] |
2 ProjectA[SummaryTask 2] | 1.4 ProjectB[Task e] | 1.2.3 ProjectC[Task e] |
2.1 ProjectA[Task e] | 2 ProjectB[SummaryTask 2] | 2 ProjectC[SummaryTask 2] |
2.1 ProjectB[Task f] | 2.1 ProjectC[Task f] |
Example of Task Data Source
(FYI when Parent Task Name = Project Name = Level 0 = Outline Number 0)
EXTRACT column depicts what I am trying to extract:
for project A, the task with outline number 1.1 ProjectA[Task a] list the ProjectA[SummaryTask 1] as the parent task in the data source
for project A, the task with outline number 1.1.1 ProjectA[Task b] list the ProjectA[Task a] as the parent task in the data source
Project ID | TaskID | TaskName | Parent Task Name | Outline Number | Outline Level | EXTRACT |
ProjectA | Axxx | Project A | 0 | 0 | ||
ProjectA | Axxx0 | 1 ProjectA[SummaryTask 1] | 0 Project A | 1 | 1 | |
ProjectA | Axxx1 | 1.1 ProjectA[Task a] | 1 ProjectA[SummaryTask 1] | 1.1 | 2 | Parent task name is ProjectA [SummaryTask 1] and the outline number for the parent task = 1 |
ProjectA | Axxx2 | 1.1.1 ProjectA[Task b] | 1.1 ProjectA[Task a] | 1.1.1 | 3 | Parent task name is 1.1 ProjectA[Task a] and the outline number for the Parent task = 1.1 |
ProjectA | Axxx3 | 1.2 ProjectA[Task c] | 1 ProjectA[SummaryTask 1] | 1.2 | 2 | |
ProjectA | Axxx4 | 1.21 ProjectA[Task d] | 1.2 ProjectA[Task c] | 1.2.1 | 3 | |
ProjectA | Axxx5 | 2 ProjectA[SummaryTask 2] | 0 Project A | 2 | 1 | |
ProjectA | Axxx1 | 2.1 ProjectA[Task e] | 2 ProjectA[SummaryTask 2] | 2.1 | 2 | |
ProjectB | Bxxx | ProjectB | 0 | 0 | ||
ProjectB | Bxxx0 | 1 ProjectB[SummaryTask 1] | Project B | 1 | 1 | |
ProjectB | Bxxx1 | 1.1 ProjectB[Task a] | 1 ProjectB[SummaryTask 1] | 1.1 | 2 | |
ProjectB | Bxxx2 | 1.2 ProjectB[Task b] | 1 ProjectB[SummaryTask 1] | 1.2 | 2 | |
ProjectB | Bxxx3 | 1.2.1 ProjectB[Task c] | 1.2 ProjectB[Task b] | 1.2.1 | 3 | |
ProjectB | Bxxx4 | 1.3 ProjectB[Task d] | 1 ProjectB[SummaryTask 1] | 1.3 | 2 | |
ProjectB | Bxxx5 | 1.4 ProjectB[Task e] | ProjectB[SummaryTask 1] | 1.4 | 2 | |
ProjectB | Bxxx6 | 2 ProjectB[SummaryTask 2] | Project B | 2 | 1 | |
ProjectB | Bxxx7 | 2.1 ProjectB[Task f] | 2 ProjectB[SummaryTask 2] | 2.1 | 2 | |
ProjectC | Cxxx | ProjectB | 0 | 0 | ||
ProjectC | Cxxx0 | 1 ProjectC[SummaryTask 1] | 0 ProjectC | 1 | ect... |
Hello @Pedagogic368
If you could provide sample .pbix file that should help provide quick work on the solution.
Regards
Kumail Raza
Bump. Well I tried all day again. Anybody anyhelp? Tnx
@Pedagogic368 I'm a newbie to DAX. Just a suggestion.
Can you use search function to try locate first occurence of word Project. If it is 3 that means Outline number is one digit only like 1, If it is 5 digit then 3 digit like 1.1 and so on.
Once you know the position of word project then use LEFT function to get the Outline Number. And do your magic with it.
May be?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |