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

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

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 AProject BProject C
000

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 IDTaskIDTaskNameParent Task NameOutline NumberOutline LevelEXTRACT
ProjectAAxxxProject A 00 
ProjectAAxxx01 ProjectA[SummaryTask 1]0 Project A11 
ProjectAAxxx11.1 ProjectA[Task a]1 ProjectA[SummaryTask 1]1.12Parent task name is ProjectA [SummaryTask 1] and the outline number for the parent task = 1
ProjectAAxxx21.1.1 ProjectA[Task b]1.1 ProjectA[Task a]1.1.13Parent task name is 1.1 ProjectA[Task a] and the outline number for the Parent task  = 1.1
ProjectAAxxx31.2 ProjectA[Task c]1 ProjectA[SummaryTask 1]1.22 
ProjectAAxxx41.21 ProjectA[Task d]1.2 ProjectA[Task c]1.2.13 
ProjectAAxxx52 ProjectA[SummaryTask 2]

0 Project A

21 
ProjectAAxxx12.1 ProjectA[Task e]2 ProjectA[SummaryTask 2]2.12 
ProjectBBxxx

ProjectB

 00 
ProjectBBxxx0

1

ProjectB[SummaryTask 1]

Project B11 
ProjectBBxxx11.1 ProjectB[Task a]

1

ProjectB[SummaryTask 1]

1.12 
ProjectBBxxx21.2 ProjectB[Task b]

1

ProjectB[SummaryTask 1]

1.22 
ProjectBBxxx31.2.1 ProjectB[Task c]1.2 ProjectB[Task b]1.2.13 
ProjectBBxxx41.3 ProjectB[Task d]

1

ProjectB[SummaryTask 1]

1.32 
ProjectBBxxx51.4 ProjectB[Task e]ProjectB[SummaryTask 1]1.42 
ProjectBBxxx62 ProjectB[SummaryTask 2]Project B21 
ProjectBBxxx72.1  ProjectB[Task f]2 ProjectB[SummaryTask 2] 2.12 
ProjectCCxxxProjectB 00 
ProjectCCxxx01 ProjectC[SummaryTask 1]0 ProjectC1ect... 

 

2 REPLIES 2
Pedagogic368
Frequent Visitor

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?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors