Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Getting row values from another row based on associated texts

Hi   I am trying to get the calculated value based on these relationships:   Project Initiation Phase - M02 Scope and Feasibility - M04 Design Phase - M12 Delivery Phase - M23 Handover/Takeov...
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi Anonymous 

     

    Try this new code to add a column:

    Calculated Value = 
VAR _A =
    SWITCH (
        TRUE (),
        [ObjectName] = "Project Initiation Phase", "M02 - Approve Fee Proposal",
        [ObjectName] = "Scope and Feasibility", "M04 - Approve Phase 1 Report",
        [ObjectName] = "Design Phase", "M12 - Approve Phase 2 Report",
        [ObjectName] = "Delivery Phase", "M23 - Issue Certificate of Completion",
        [ObjectName] = "Hand Over/Take Over Phase", "M24 - Recommend Hand Over Acceptance",
        [ObjectName] = "Project Close Phase", "M25 - Reduce PO Values To Zero",
        [ObjectName] = "Scope and Feasibility Phase", "Scope and Feasibility Phase",
        BLANK ()
    )
VAR _B =
    IF (
        ISBLANK ( _A ),
        BLANK (),
        CALCULATE (
            MAX ( 'Table'[ActualFinishDate] ),
            FILTER (
                ALL ( 'Table' ),
                [ObjectName] = _A
                    && [ProjectName] = EARLIER ( 'Table'[ProjectName] )
            )
        )
    )
RETURN
    IF (
        ISBLANK ( _B ),
        CALCULATE (
            MAX ( 'Table'[FinishDate] ),
            FILTER (
                ALL ( 'Table' ),
                [ObjectName] = _A
                    && [ProjectName] = EARLIER ( 'Table'[ProjectName] )
            )
        ),
        _B
    )

     

     

     

     

    Output:

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

Hi Anonymous 

 

Try this code to add a new column to your table:

Calculated Value =
VAR _A =
    SWITCH (
        TRUE (),
        [ObjectName] = "Project Initiation Phase", "M02",
        [ObjectName] = "Scope and Feasibility", "M04",
        [ObjectName] = "Design Phase", "M12",
        [ObjectName] = "Delivery Phase", "M23",
        [ObjectName] = "Hand Over/Take Over Phase", "M24",
        [ObjectName] = "Project Close Phase", "M25",
        [ObjectName] = "Scope and Feasibility Phase", "Scope and Feasibility Phase",
        BLANK ()
    )
RETURN
    IF (
        ISBLANK ( _A ),
        BLANK (),
        CALCULATE (
            MAX ( 'Table'[ActualFinishDate] ),
            FILTER (
                ALL ( 'Table' ),
                [ObjectName] = _A
                    && [ProjectName] = EARLIER ( 'Table'[ProjectName] )
            )
        )
    )

 

Output:

 

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks Vahid-that was great!

 

However, I was testing the data and had another issue you might be able to help. Sometimes, the data will come up with 00/00/0000 for the actual finish and then I have to search for another finish date field. In this example, when project close for project name = 2000, the value is 00/00/0000, so I will have to use another date field (column 5) in M25.. so the calculated field should become 11/10/2021 instead of 00/00/0000 for project 2000 during project close. 

 

Hope this is clear.

 

 

ProjectNameObjectNameForecastFinishDateActualFinishDateFinishDateCalculated
1000Delivery Phase20/05/202027/08/202030.04.202030/04/2020
1000Design Phase14/06/201911/06/201911.06.201928/05/2019
1000Hand Over/Take Over Phase10/12/20194/02/202124.02.20214/02/2021
1000M02 - Approve Fee Proposal00/00/00005/02/201805.02.2018 
1000M04 - Approve Phase 1 Report00/00/000012/07/201812.07.2018 
1000M12 - Approve Phase 2 Report00/00/000028/05/201911.06.2019 
1000M23 - Issue Certificate of Completion00/00/000030/04/202030.04.2020 
1000M24 - Recommend Hand Over Acceptance00/00/00004/02/202124.02.2021 
1000M25 - Reduce PO Values To Zero00/00/00004/02/202111.03.2021 
1000Project Close Phase10/12/201900/00/000030.04.20214/02/2021
1000Project Initiation Phase23/01/20185/02/201805.02.20185/02/2018
1000Scope and Feasibility Phase28/05/201812/07/201812.07.201812/07/2018
2000Delivery Phase25/11/201920/11/201920.11.201920/11/2019
2000Design Phase10/12/20166/04/201806.04.20186/04/2018
2000Hand Over/Take Over Phase30/09/201923/07/202021.02.20204/08/2020
2000M02 - Approve Fee Proposal00/00/00001/06/201501.06.2015 
2000M04 - Approve Phase 1 Report00/00/000022/10/201624.10.2016 
2000M12 - Approve Phase 2 Report00/00/00006/04/201806.04.2018 
2000M23 - Issue Certificate of Completion00/00/000020/11/201920.11.2019 
2000M24 - Recommend Hand Over Acceptance00/00/00004/08/202021.02.2020 
2000M25 - Reduce PO Values To Zero00/00/000000/00/000011.10.2021 
2000Project Close Phase22/10/202000/00/000019.02.202100/00/0000
2000Project Initiation Phase1/06/20151/06/201501.06.20151/06/2015
2000Scope and Feasibility Phase22/10/201622/10/201624.10.201622/10/2016
3000………… 
3000………… 

 

 

 

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    How did you find that 11/10/2021 instead of 00/00/0000 for project close project 2000? there is no date like that in your table?

     

    can you add more details?

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Vahid,

       

      Can you see the updated dataset I have provided you above?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        See this particular row in the dataset.

         

        2000M25 - Reduce PO Values To Zero00/00/000000/00/000011.10.2021 
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

       

       

      Hi Vahid,

       

      See this updated dataset-realised that it wasn't updated properly previously. I need to use the 5th column data (See the orange in M25 in particular) for the calculated field. 

       

      ProjectNameObjectNameForecastFinishDateActualFinishDateFinishDateCalculated
      1000Delivery Phase20/05/202027/08/202030.04.202030/04/2020
      1000Design Phase14/06/201911/06/201911.06.201928/05/2019
      1000Hand Over/Take Over Phase10/12/20194/02/202124.02.20214/02/2021
      1000M02 - Approve Fee Proposal00/00/00005/02/201805.02.2018 
      1000M04 - Approve Phase 1 Report00/00/000012/07/201812.07.2018 
      1000M12 - Approve Phase 2 Report00/00/000028/05/201911.06.2019 
      1000M23 - Issue Certificate of Completion00/00/000030/04/202030.04.2020 
      1000M24 - Recommend Hand Over Acceptance00/00/00004/02/202124.02.2021 
      1000M25 - Reduce PO Values To Zero00/00/00004/02/202111.03.2021 
      1000Project Close Phase10/12/201900/00/000030.04.20214/02/2021
      1000Project Initiation Phase23/01/20185/02/201805.02.20185/02/2018
      1000Scope and Feasibility Phase28/05/201812/07/201812.07.201812/07/2018
      2000Delivery Phase25/11/201920/11/201920.11.201920/11/2019
      2000Design Phase10/12/20166/04/201806.04.20186/04/2018
      2000Hand Over/Take Over Phase30/09/201923/07/202021.02.20204/08/2020
      2000M02 - Approve Fee Proposal00/00/00001/06/201501.06.2015 
      2000M04 - Approve Phase 1 Report00/00/000022/10/201624.10.2016 
      2000M12 - Approve Phase 2 Report00/00/00006/04/201806.04.2018 
      2000M23 - Issue Certificate of Completion00/00/000020/11/201920.11.2019 
      2000M24 - Recommend Hand Over Acceptance00/00/00004/08/202021.02.2020 
      2000M25 - Reduce PO Values To Zero00/00/000000/00/000011.10.2021 
      2000Project Close Phase22/10/202000/00/000019.02.202111/10/2021
      2000Project Initiation Phase1/06/20151/06/201501.06.20151/06/2015
      2000Scope and Feasibility Phase22/10/201622/10/201624.10.201622/10/2016
      3000………… 
      3000…………