Getting Ready

To prepare for this recipe, do the following:

1. Open Power BI Desktop.
2. Use an Enter Data query to create a table called R04_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

3. Use another Enter Data query to create a table called R04_Resources that contains the following data:

Cost_Per_Hour	Resource_Name
95	Greg
85	Julie
75	Pam
75	Mike

4. Create a table called R04_Calendar using the following formula:

R04_Calendar = CALENDAR(DATE(2020,1,1),DATE(2020,6,30))

5. Create another table called R04_Hours using the following formula:

R04_Hours = 
    VAR __Greg = SELECTCOLUMNS({ ("Greg", 8) },"Resource Name",[Value1],"Hours",[Value2])
    VAR __Julie = SELECTCOLUMNS({ ("Julie", 8) },"Resource Name",[Value1],"Hours",[Value2])
    VAR __Pam = SELECTCOLUMNS({ ("Pam", 8) },"Resource Name",[Value1],"Hours",[Value2])
    VAR __TaskID3 = SELECTCOLUMNS({ (3, DATE(2020,1,13)) },"ID",[Value1],"Date",[Value2])
    VAR __TaskID4 = 
        SELECTCOLUMNS( 
            ADDCOLUMNS(
                GENERATESERIES(DATE(2020,1,14),DATE(2020,1,27),1),
                "ID",4
            ),
            "ID",[ID],"Date",[Value]
        )
    VAR __TaskID5 = 
        SELECTCOLUMNS( 
            ADDCOLUMNS(
                GENERATESERIES(DATE(2020,1,28),DATE(2020,1,31),1),
                "ID",5
            ),
            "ID",[ID],"Date",[Value]
        )
    VAR __TaskID7 = 
        SELECTCOLUMNS( 
            ADDCOLUMNS(
                GENERATESERIES(DATE(2020,2,4),DATE(2020,2,12),1),
                "ID",7
            ),
            "ID",[ID],"Date",[Value]
        )
    VAR __TaskID8 = 
        SELECTCOLUMNS( 
            ADDCOLUMNS(
                GENERATESERIES(DATE(2020,2,25),DATE(2020,3,12),1),
                "ID",8
            ),
            "ID",[ID],"Date",[Value]
        )
    VAR __TaskID9 = 
        SELECTCOLUMNS( 
            ADDCOLUMNS(
                GENERATESERIES(DATE(2020,3,31),DATE(2020,4,1),1),
                "ID",9
            ),
            "ID",[ID],"Date",[Value]
        )
    VAR __Table1 = 
        UNION(
            GENERATE(__Greg, __TaskID3), 
            GENERATE(__Julie,__TaskID3), 
            GENERATE(__Pam, __TaskID3)
        )
    VAR __Table2 = UNION(GENERATE(__Greg, __TaskID4), GENERATE(__Julie,__TaskID4))
    VAR __Table3 = GENERATE(__Julie,__TaskID5)
    VAR __Table4 = UNION(GENERATE(__Greg, __TaskID7), GENERATE(__Julie,__TaskID7))
    VAR __Table5 = GENERATE(__Greg,__TaskID8)
    VAR __Table6 = GENERATE(__Julie,__TaskID9)
RETURN
    UNION(__Table1, __Table2, __Table3, __Table4, __Table5, __Table6)

6. Create a relationship between the ID column in the R04_Project table and the ID column in the R04_Hours table.
7. Create a relationship between the Resource_Name column in the R04_Hours table and the Resource_Name column in the R04_Resources table. Ensure that this relationship has a Cross filter direction of Both.
8. Ensure that no other relationships exist in the data model for the tables that were created.

The R04_Project table represents the project plan. This project plan has three phases and nine tasks. The Scheduled_Work column in the R04_Project table shows the number of hours each task is expected to complete. The R04_Hours table represents the hours of work that project resources assigned to each task have reported as expended against each task. The Date column within this table is the date upon which the reported hours were worked. Finally, the R04_Resources table provides the hourly cost for each resource when working on the project.