Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Add Conditional Tasks to Existing Schedules

Hello All, I’m trying to add conditional tasks into an existing schedule within Power BI and I’m not sure how to setup applied steps vs. doing all of this manually every time.   I have an existing...
Need Help
v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Anonymous ,

 

We can use the following steps to meet your requirement:

 

1. In the Power Query Editor, we can merge two tables based on the [Task to Reference] and then we can expand the column Project ID, Start and finish

 

2. Then we can create new calculated columns [start] and [end]. 

start = Merge1[Table.Start]+[Duration]
finish = [start]+[Duration]

Could you please share the logic why Task 2  add 7 days in finish while it has 15 duration?

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?


Best regards,

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

v-lid-msft Thank you for the quick reply,

So far when I merge the tables with the settings you've shown and then expand the few columns I get null values for everything. I'm wondering if the fact that there other levels to the heirarchy is affecting the results. 

 

For example: 

Task 1 exists in North America, LATAM, and EMEA for the same project number. So within Project ABC123 there are multiple Start/Finish dates for Task 1 depending on the region. Does that impact how we would approach this?

 

The way I've added tasks manually in the past was to unpivot my task column, then add a new column with the specific task, populate with a formula referencing the other applicable task, then once again pivoting the data into a task column inclusive of my newly added column. This works...but for 150 columns it's a real pain... 

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can try to create two calculated columns to meet your requirement instead of merge in power query editor.

     

    start_column = [Start]+CALCULATE(SUM('Table (2)'[Duration]),'Table (2)'[Task to Reference] = EARLIER('Table'[Task]))
    finish_column = [start_column]+CALCULATE(SUM('Table (2)'[Duration]),'Table (2)'[Task to Reference] = EARLIER('Table'[Task]))

     

     

    Then we can get the result like this,

     

     

     

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

     

    BTW, pbix as attched.


    Best regards,

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-lid-msft ,

      I apologize for the delayed response. With all of the COVID-19 issues this task has temporarily taken a backseat at work. I truly appreciate your assistance and once I'm able to get back to this topic I will post a response on how your solution worked.