DAX Expression to extract value from one column based on a different column

[Pedagogic368]

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

• Question - How do I extract the outline number for each of Parent Task. Because there are several different projects in the source data I will have multiple instances of the same outline number and this keeps causing an error in my data.

Task Table 

Mutliple projects (Project A, ProjectB, and Project C)

-Each project (A, B and C) has outline numbers associated with different task

• There are multiple instances of the same outline number accross the different projects

-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

• The outline number for ProjectA[SummaryTask1] = 1

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

• The outline number for ProjectA[Task a] = 1.1

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

[Kumail]

Hello @Pedagogic368 

If you could provide sample .pbix file that should help provide quick work on the solution.

Regards

Kumail Raza

[Pedagogic368]

Bump. Well I tried all day again. Anybody anyhelp? Tnx

[Anonymous]

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