Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |