Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
calvgo
Helper I
Helper I

Getting row values from another row based on associated texts

‎12-19-2021 08:50 PM

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/Takeover - M24

Project Close - M 25

 

Sample data on different projects with the required calculated values is as shown:

 

ProjectNameObjectNameForecastFinishDateActualFinishDateCalculated value
1000Delivery Phase20/05/202027/08/202030/04/2020
1000Design Phase14/06/201911/06/201928/05/2019
1000Hand Over/Take Over Phase10/12/20194/02/2021

4/02/2021

1000M0200/00/00005/02/2018 
1000M04 00/00/000012/07/2018 
1000M12 00/00/000028/05/2019 
1000M23 00/00/000030/04/2020 
1000M2400/00/00004/02/2021 
1000M2500/00/00004/02/2021 
1000Project Close Phase10/12/201900/00/00004/02/2021
1000Project Initiation Phase23/01/20185/02/20185/02/2018
1000Scope and Feasibility Phase28/05/201812/07/201812/07/2018
2000Delivery Phase25/11/201920/11/201920/11/2019
2000Design Phase10/12/20166/04/20186/04/2018
2000Hand Over/Take Over Phase30/09/201923/07/20204/08/2020
2000M0200/00/00001/06/2015 
2000M0400/00/000022/10/2016 
2000M1200/00/00006/04/2018 
2000M23 00/00/000020/11/2019 
2000M2400/00/00004/08/2020 
2000M2500/00/000000/00/0000 
2000Project Close Phase22/10/202000/00/000000/00/0000
2000Project Initiation Phase1/06/20151/06/20151/06/2015
2000Scope and Feasibility Phase22/10/201622/10/201622/10/2016
3000…………
3000…………

 

 

 

Message 1 of 13
1,288 Views
0
1 ACCEPTED SOLUTION
VahidDM
Super User
Super User
In response to calvgo

‎12-20-2021 02:06 PM

Hi @calvgo 

 

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:

VahidDM_0-1640038003244.png

 

 

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/

 

 

View solution in original post

Message 10 of 13
1,119 Views
12 REPLIES 12
VahidDM
Super User
Super User

‎12-19-2021 08:52 PM

Hi @calvgo 

 

Can you post sample data as text and expected output?
Not enough information to go on, and it's not clear for me!

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Message 2 of 13
1,285 Views
0
calvgo
Helper I
Helper I
In response to VahidDM

‎12-19-2021 08:58 PM

 

ProjectNameObjectNameForecastFinishDateActualFinishDateCalculated value
1000Delivery Phase20/05/202027/08/202030/04/2020
1000Design Phase14/06/201911/06/201928/05/2019
1000Hand Over/Take Over Phase10/12/20194/02/20214/02/2021
1000M0200/00/00005/02/2018 
1000M04 00/00/000012/07/2018 
1000M12 00/00/000028/05/2019 
1000M23 00/00/000030/04/2020 
1000M2400/00/00004/02/2021 
1000M2500/00/00004/02/2021 
1000Project Close Phase10/12/201900/00/00004/02/2021
1000Project Initiation Phase23/01/20185/02/20185/02/2018
1000Scope and Feasibility Phase28/05/201812/07/201812/07/2018
2000Delivery Phase25/11/201920/11/201920/11/2019
2000Design Phase10/12/20166/04/20186/04/2018
2000Hand Over/Take Over Phase30/09/201923/07/20204/08/2020
2000M0200/00/00001/06/2015 
2000M0400/00/000022/10/2016 
2000M1200/00/00006/04/2018 
2000M23 00/00/000020/11/2019 
2000M2400/00/00004/08/2020 
2000M2500/00/000000/00/0000 
2000Project Close Phase22/10/202000/00/000000/00/0000
2000Project Initiation Phase1/06/20151/06/20151/06/2015
2000Scope and Feasibility Phase22/10/201622/10/201622/10/2016
3000…………
3000…………
Message 3 of 13
1,248 Views
0
calvgo
Helper I
Helper I
In response to calvgo

‎12-19-2021 09:03 PM

Essentially, I am trying to get the actual finish date from all the M labels i.e. M02, M04, M23 etc into the various phases. One example would be getting the M02 -actual finished into the project initation phase for project name 1000- so it would be 05/02/2018 as the newly calculated field in Project Initiation Phase.

 

Hope this is clear. 

Message 4 of 13
1,241 Views
0
VahidDM
Super User
Super User
In response to calvgo

‎12-19-2021 09:26 PM

Hi @calvgo 

 

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:

 

VahidDM_0-1639977965347.png

 

 

 

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/

 

 

Message 5 of 13
1,200 Views
0
calvgo
Helper I
Helper I
In response to VahidDM

‎12-19-2021 10:25 PM

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………… 

 

 

 

Message 6 of 13
1,178 Views
VahidDM
Super User
Super User
In response to calvgo

‎12-19-2021 11:07 PM

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/

 

 

Message 7 of 13
1,169 Views
0
calvgo
Helper I
Helper I
In response to VahidDM

‎12-20-2021 01:15 AM

 

 

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………… 
Message 13 of 13
1,155 Views
0
calvgo
Helper I
Helper I
In response to VahidDM

‎12-19-2021 11:39 PM

Hi Vahid,

 

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

Message 8 of 13
1,163 Views
0
calvgo
Helper I
Helper I
In response to calvgo

‎12-19-2021 11:41 PM

See this particular row in the dataset.

 

2000M25 - Reduce PO Values To Zero00/00/000000/00/000011.10.2021 
Message 9 of 13
1,163 Views
0
VahidDM
Super User
Super User
In response to calvgo

‎12-20-2021 02:06 PM

Hi @calvgo 

 

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:

VahidDM_0-1640038003244.png

 

 

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/

 

 

Message 10 of 13
1,120 Views
calvgo
Helper I
Helper I
In response to VahidDM

‎12-20-2021 10:09 PM

Thanks Vahid-that was brilliant scripting that helped a lot! 🙂

Message 11 of 13
1,083 Views
VahidDM
Super User
Super User
In response to calvgo

‎12-20-2021 10:12 PM

No Worries 🙏

Message 12 of 13
1,075 Views
0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

View All