Get amount based on data in 3 different columns

[malikara46]

Hello everyone, 

we have a project table where for each project we save the date of a milestone that has been passed. Milestone 1 must be passed first, than milestone 2 and than milestone 3. If a project passed all 3 milestones, all 3 columns will contain a date (see Project YXZ below)  

Project Name	Milestone 1	Milestone 2	Milestone 3
ABC	05.10.2020
YXZ	04.04.2019	04.12.2020	13.11.2021
GHL	05.10.2020
REW

For each milestone we want to show the amount of projects that passed this milestone and the project name(s).

So the desired results looks like this: 

Milestone	Number of Projects	Project Names
Milestone 1	2	ABC GHL
Milestone 2	0
Milestone 3	1	YXZ

The challenge where I am struggling is that we want distinct counts. So each project should be counted and displayed only once. If for example a project has reached all three milestones, it should not be counted in each milestone, but only in Milestone 3. 

This rule always applies so a different scenario is when a project has reached milestone 2, it should not be displayed or counted in milestone 1 but only in milestone 2. 

If a project has not passed any milestones it should not be counted or displayed on the table. 

Really appreciating any help or hint. 

[V-lianl-msft · Accepted solution]

Please try to unpivot  these Milestone columns.

Create a new column to determine the last milestone experienced by the project.

End_of_Milestone = 
var max_date = CALCULATE(MAX('Table (2)'[Date]),ALLEXCEPT('Table (2)','Table (2)'[Project Name]))
return IF('Table (2)'[Date]=max_date,'Table (2)'[Milestone])

Finally, create two measures to get the results you expect:

NUmber_P = COUNT('Table (2)'[End_of_Milestone])+0

Measure 2 = CONCATENATEX (
            FILTER('Table (2)',NOT(ISBLANK('Table (2)'[End_of_Milestone]))),
            'Table (2)'[Project Name],
            ", ",             
            'Table (2)'[Project Name],   
            ASC               
        )

[HotChilli]

If you stick with the original table format, you could add a column which checks each column for blank (depending on the status hierarchy e.g.:

IF (NOT ISBLANK(TablePOrig[Milestone 3]), "Milestone3",....then milestone2 and so on

---

You could also Unpivot the data and add a marker column to indicate the latest status.