Creating custom column for each X return non-null value

[Anonymous]

Hi all,

I am looking at project management data from Microsoft Project Online (MSPO). I have 2 tables:

1) The project table: contains macro information for each project

2) The task table: contains information about the tasks that are contained in each project.  It looks as follow:

Project	Parent task	Task	Date	Launch date
Project 1	Greenlight	Task 1	Dec 3 2018
Project 1	Greenlight	Task 2	Dec 10 2018
Project 1	Execute	Task 1	Jan 5 2018
Project 1	Execute	Execute complete	Jan 10 2018	Jan 10 2018
Project 1	Launch	Task 1	Jan 11 2018
Project 1	Launch	Task 2	Feb 20 2018

I have created the variable Task.Launch Date such as: 

Launch_Date = IF(AND(Tasks[ParentTaskName]="Execute",Tasks[TaskIsMilestone]=True),Tasks[TaskFinishDate],BLANK())

Key question: How do I create a LaunchDate variable on the project table which has the Task.Launch Date value when it is not null?

I have tried a LOOKUP (LOOKUPVALUE(Tasks[Launch_Date],Tasks[ProjectId],Projects[ProjectId])) and telling PowerBI to ignore blank values ALLNOBLANKROW(Tasks[Launch_Date]) but neither is working. I get the error "A table of multiple values was supplied where a single value was expected."

Any help would be greatly appreciated!

[v-lili6-msft · Accepted solution]

HI, @Anonymous

Projects table have a one-to-many relationship with tasks table, so in your calculation, it should returns multiple values

for each row although you had excluded null value, but if there are two or more Launch date,

which Launch date should be returned for the current row?

So you could use this formula to create a calculate column:

Column = CALCULATE(MAX(Tasks[Launch_Date]),FILTER(Tasks,Tasks[ProjectId]=Projects[ProjectId]))

Best Regards,

Lin

[Anonymous]

That worked perfectly, thank you for your help @v-lili6-msft!