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...
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               
        )

     

     

V-lianl-msft's avatar
V-lianl-msft
Icon for Community Support rankCommunity 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               
        )