Forum Discussion

Pedagogic368's avatar
Pedagogic368
Frequent Visitor
5 years ago
Solved

How to use Task Outline Numbers from MS Project to group together task in Power BI

I have a MS Project with Site, Lot#, and Task Name. Each task has a start Finish Date and % complete. The Task Outline Number corresponds by site, Lot# and Task. For example: Site Lot# Task Nam...
  • stevedep's avatar
    stevedep
    5 years ago

    Like this?

    Measure2 = 
VAR _sel = SELECTEDVALUE('Table'[Task Outline Number])
VAR _NR = LEFT(_sel,3)
RETURN
CALCULATE(MIN('Table'[Finish Date].[Date]), FILTER(ALLSELECTED('Table'), [Task Name ] = "Release" &&  LEFT([Task Outline Number],3) = _NR))

    File is attached.

     

    Kind regards, Steve. 

     

  • stevedep's avatar
    stevedep
    5 years ago

    Then this should be it?

    Measure2 = 
VAR _sel = SELECTEDVALUE('Table'[Task Outline Number])
VAR _NR = LEFT(_sel,3)
RETURN
CALCULATE(MIN('Table'[Finish Date].[Date]), FILTER(ALLSELECTED('Table'), CONTAINSSTRING([Task Name ],"Release")  &&  LEFT([Task Outline Number],3) = _NR))

    File is attached.

    Kind regards, Steve. 

Pedagogic368's avatar
Pedagogic368
Frequent Visitor
5 years ago

Update on the table information given above. 

The task ID is unique to each summary and there are Task Outline levels associated with each task (ie Task Outline Level - Parent summary task = Level 3  and child summary task (Initiate) = Level 4  . The report = Level 5)

Example for Chicago I want to link the Release Report (Level 5) Date to the Initiate Date on Level 4 :

Chicago. - The Parent Summary Task (Level 3)  Task Outline Number  = 1.1.1

Chicago - Initiate. Child Summary Task (Level 4) = 1.1.1.2

Chicago - Report. (Level 5) Task Outline Number 1.1.1.2.4

 

Would I first form a Group of all the task under 1.1.1.X (write a DAX that says group first four digits). Extract the Finish Date associated with Initiate and then add +30edays to that date to calculate the Finish date the Report should be completed? Thoughts

Pedagogic368's avatar
Pedagogic368
Frequent Visitor
5 years ago

So far no help. So let me ask one more time for some advice. ðŸ˜”

Here is where I am at. 

Each "grouping" has a similar TaskOutline number. So as I mentioned previously - 

Group A

Chicago is 1.1.1

Chicago- Initiation is 1.1.1.2

Chicago-Report is 1.1.1.4.2 

I truncated all to a value of 1.1.1 

 

Group B

Indiana is 2.1.1

Indiana - Initiation is 2.1.1.2

Indiana -Report is 2.1.1.4.2 

I truncated all to a value of 2.1.1 

 

So, as you can see I have several differnt "groups" what I need to do is write a DAX expression that search within each group the Finish Date for the XXXXX-Initiate in each group and then the Start Date for the XXXXX-Report in the same group. HOW DO I DO THIS? THANKS in advance for your help. Please! ðŸ˜¶