Forum Discussion

malikara46's avatar
malikara46
Frequent Visitor
4 years ago
Solved

Get amount based on data in 3 different columns

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 NameMilestone 1  Milestone 2  Milestone 3
ABC05.10.2020  
YXZ04.04.201904.12.202013.11.2021
GHL05.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      1YXZ

 

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. 

Need Help
Tips and Tricks
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    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               
        )

     

     

2 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    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. 

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    4 years ago

    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               
        )