To prepare for this recipe, do the following:
- Open Power BI Desktop.
- Use an Enter Data query to create a table called R02_Project that contains the following data:
|
ID |
Project |
Phase |
Name |
Scheduled_Work |
Start_Date |
Finish_Date |
|
3 |
The Project |
Phase 1 |
Task 1 |
24 |
1/13/2020 |
1/13/2020 |
|
4 |
The Project |
Phase 1 |
Task 2 |
160 |
1/14/2020 |
1/27/2020 |
|
5 |
The Project |
Phase 1 |
Task 3 |
40 |
1/28/2020 |
2/3/2020 |
|
7 |
The Project |
Phase 2 |
Task 4 |
240 |
2/4/2020 |
2/24/2020 |
|
8 |
The Project |
Phase 2 |
Task 5 |
200 |
2/25/2020 |
3/30/2020 |
|
9 |
The Project |
Phase 2 |
Task 6 |
160 |
3/31/2020 |
4/27/2020 |
|
11 |
The Project |
Phase 3 |
Task 7 |
120 |
4/28/2020 |
5/4/2020 |
|
12 |
The Project |
Phase 3 |
Task 8 |
240 |
5/5/2020 |
5/25/2020 |
|
13 |
The Project |
Phase 3 |
Task 9 |
80 |
5/26/2020 |
6/8/2020 |
- Use another Enter Data query to create a table called R02_Assignments that contains the following data:
|
ID |
Scheduled_Work |
Resource_Name |
|
3 |
8 |
Greg |
|
3 |
8 |
Julie |
|
3 |
8 |
Pam |
|
4 |
80 |
Greg |
|
4 |
80 |
Julie |
|
5 |
40 |
Julie |
|
7 |
120 |
Greg |
|
7 |
120 |
Julie |
|
8 |
200 |
Greg |
|
9 |
160 |
Julie |
|
11 |
40 |
Greg |
|
11 |
40 |
Pam |
|
11 |
40 |
Mike |
|
12 |
120 |
Pam |
|
12 |
120 |
Mike |
|
13 |
80 |
Pam |
- Use another Enter Data query to create a table called R02_Resources that contains the following data:
|
Cost_Per_Hour |
Resource_Name |
|
95 |
Greg |
|
85 |
Julie |
|
75 |
Pam |
|
75 |
Mike |
- Create a table called R02_Calendar using the following formula:
R02_Calendar = CALENDAR(DATE(2020,1,1),DATE(2020,6,30))
- Create a relationship between the ID column in the R02_Project table and the ID column in the R02_Assignments table.
- Create a relationship between the Resource_Name column in the R02_Assignments table and the Resource_Name column in the R02_Resources table. Ensure that this relationship has a Cross filter direction of Both.
- Ensure that no other relationships exist in the data model for the tables that we created.
The R02_Project table represents the project plan. This project plan has three phases and nine tasks. The Scheduled_Work column in the R02_Project table provides the number of hours each task is expected to take to complete. The R02_Assignments table represents the resources assigned to each task and the number of hours (Scheduled_Work) these resources are expected to work on each task. Finally, the R02_Resources table provides the hourly per-resource cost for working on the project.