Forum Discussion
Creating custom column for each X return non-null value
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:
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!
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
2 Replies
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
That worked perfectly, thank you for your help v-lili6-msft!