Join tables - Get a column from the most recent date in a colum (Project, Project Status tables)

[dude95]

Seems like this shouldn't be that hard, but I'm struggling.  I have two tables

Project Table

Project ID

0000001

0000002

ProjectStatus Table

Project ID	Status Date	Team	StatusInd
0000001	12/19/20	PMO	Red
0000001	12/12/20	PMO	Green

There are several reports I would like to show the latest(most recent) statusInd for a Project hardcoded to PMO team.  In the example, want to show Project 000001 = Red.

Should this just be a measure or Dax?  What would the formulat look like.  Been searching, but haven't gotten exactly what I need.  TIA.

[dude95]

This was perfect - I just added this to the end so that all projects would still be displayed even if they didn't have a PMO status report submitted yet.  Thanks for a great solution!

var _AllStatus = 
concatenate(_Status, "")
return
_AllStatus

[Fowmy · Accepted solution]

@dude95 

Create a table visual and insert the Project ID from Project Table and create the following measure

Measure = 
var _Date = 
MAXX(
    FILTER(
        Proejctstatus,
        Proejctstatus[project id] = SELECTEDVALUE(Project Table[Project ID])
    ),
    Proejctstatus[Status Date]
) 

var _Status = 
MAXX(
    FILTER(
        Proejctstatus,
        Proejctstatus[project id] = SELECTEDVALUE(Project Table[Project ID]) && Proejctstatus[Status Date] = _Date 
    ),
    Proejctstatus[StatusInd]
) 

return
_Status 

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn