topic Re: DAX Lookup Value in another table with filter (calculated column) in DAX Commands and Tips

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

bhalicki — Sat, 31 Aug 2024 04:30:18 GMT

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.

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

MattAllington — Sat, 31 Aug 2024 07:02:32 GMT

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}

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

tamerj1 — Sat, 31 Aug 2024 20:59:16 GMT

Hi bhalicki

As an example:

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