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. 

stevedep's avatar
stevedep
Memorable Member
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. 

 

  • Pedagogic368's avatar
    Pedagogic368
    Frequent Visitor
    5 years ago

    Hi Steve, thank you very much for your reply and your attached file. I have a few questions (almost there!). Here is your example with my data.

    Measure3 =
    Var _sel = SELECTEDVALUE('Tasks'[TaskOutlineNumber])
    Var _NR = LEFT(_sel,3)//  Creating a "grouping" based off of the first three digits of the TaskOutlineNumber - correct?
    RETURN
    CALCULATE(MIN('Tasks'[TaskFinishDate].[Day]), FILTER(ALLSELECTED('Tasks'),[TaskName]="TO Release" && LEFT([TaskOutlineNumber],3) = _NR))//What is the FILTER doing here? Looking for any task with a Task Name that contains "TO Release" and is within the grouping? If I understood it better I might be able to make this work. Thanks so much! 
  • Pedagogic368's avatar
    Pedagogic368
    Frequent Visitor
    5 years ago

    Steve. thanks so much for this helpful information. Can you kindly tell me how I would change the filter portion the code so that it does not have to = "release" but contains in the TASKNAME field the word release.

     

    Right now it has to = (equal) "release" 

     

    Thanks so much, 

     

     

    • stevedep's avatar
      stevedep
      Memorable Member
      5 years ago

      Hi,

      Welcome,

      Not sure what you mean?

      • Pedagogic368's avatar
        Pedagogic368
        Frequent Visitor
        5 years ago

        HI Steve.

         

        Currently in the Return we have a FILTER on the taskName so that it returns only those Task with the Name that equals (exactly) the word release.  However, the task name(s) actually "contains" the word "release".

         

        So it would look something like this (but I cannot get the CONTAIN function to work). Does this help explain it? 

         

        RETURN

        CALCULATE (MIN('Table'[Finish Date].[Date]), CONTAINS(ALLSELECTED('Table'), [Task Name] "Release"

        Thanks -

        BTW I have been responding but not all my responses get posted. I really appreciate your help. I am so close!