Max Health Value based on Main Project

[ferwe]

Hello

I can't find a working solution for my problem.

I have a table named ht_ProjectData with Projects and Phases.

I want a additional column for the "Project Health". It should show the highest value from all Phases related to this Project.

The table looks like this:

MainProject	TaskName	Health	ProjectType
Project A	Project A	-	Project
Project A	Phase 1	1	Phase
Project A	Phase 2	1	Phase
Project A	Phase 3	2	Phase
Project B	Project B	-	Project
Project B	Phase 1	3	Phase
Project B	Phase 2	1	Phase
Project B	Phase 3	1	Phase

At the end it should look like this:

MainProject	TaskName	Health	ProjectType	MainProjectHealth
Project A	Project A	-	Project	2
Project A	Phase 1	1	Phase	-
Project A	Phase 2	1	Phase	-
Project A	Phase 3	2	Phase	-
Project B	Project B	-	Project	3
Project B	Phase 1	3	Phase	-
Project B	Phase 2	1	Phase	-
Project B	Phase 3	1	Phase	-

I tried this Calculation but it didn't work:

MainProjectHealth = CALCULATE(
                                      MAX(
                                         ht_ProjectData[Health];
                                         FILTER(
                                            ht_ProjectData;
                                            ht_ProjectData[ProjectType] = "Phase" &&
                                            ht_ProjectData[MainProject] = ht_ProjectData[TaskName]
                                            )
                                        )
                                   )

Any suggestions?

[Mariusz]

Hi @ferwe 

Please see the attached file with Power Query solution

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Greg_Deckler · Accepted solution]

I believe:

MainProjectHealth Column =
  IF(
    [ProjectType] = "Project",
    MAXX(
      FILTER(
        'Table',
        [MainProject] = EARLIER([MainProject]) &&
        [ProjectType] = "Phase"
      ),
      [Health]
    ),
    BLANK()
  )