DAX Lookup Value in another table with filter (calculated column)

[bhalicki]

Hi all,

I have the following tables, relationships and data:
Project:

Task:

ProjectPhaseSummary:

I want to add calculated columns to "ProjectPhaseSummary", so that I end up with the following:

Hoping someone can please suggest the most accepted way of generating the information above with DAX (preferably using CALCULATE).

Thanks,

Ben.

[tamerj1 · Accepted solution]

Hi @bhalicki 

As an example:

PlanningStarDate =
MAXX (
FILTER ( Project, Project[ProjectId] = ProjectPhaseSummary[ProjectId] ),
MAXX (
FILTER ( RELATEDTABLE ( Task ), Task[Phase] = "Planning" ),
Task[StartDate]
)
)

[MattAllington]

If you have some flex on the exact layout, you should be able to do it with minimal DAX. 
add a matrix 

put project[proj id] onto rows

put task[phase] onto columns

Write this measure and add it to values

date range = SELECTEDVALUE(Task[startdate]) & " - " & SELECTEDVALUE(Task[enddate])

with your current data, the 3 phases will not sort correctly. You could add a sort order column to fix this. If it were me, I would create a new phase dim table in power query and add a sort column, 1 for planning, 2 for design, 3 for execute. Load it into the model join it to the task table, then sort the dim text column by the sort column. Replace the phase column in the visual (remove the phase from task and add phase from the dim table}