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

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!

     

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

        Thank you so much, this IS GREAT! I could not figure out how to add the CONTAINSSTRING.

         

        MUCH APPRECIATED