Forum Discussion
Helper VDAX Lookup Value in another table with filter (calculated column)
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.
2 Replies
- MattAllington
Community Champion
If you have some flex on the exact layout, you should be able to do it with minimal DAX.
add a matrixput 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}
