Setting Up Data Relationships in Power BI

Step 1: Define Relationships in Power BI

1. Open Power BI Desktop.
2. Import your data sets. Load GeneralLedger and TimeKeeping tables.

Step 2: Create Relationships

1. Navigate to the Relationships view.
2. Identify the common key. (e.g., EmployeeID or Date).

Define Relationship between GeneralLedger and TimeKeeping

Assuming the common key is Date:

3. Drag and drop Date column from GeneralLedger to Date column in TimeKeeping.

Step 3: Create DAX Measures

1. Go to the Data view.

2. Create necessary DAX measures.

Example DAX Measures

Measure 1: Total Hours (from TimeKeeping)

Total Hours = SUM(TimeKeeping[Hours])

Measure 2: Total Cost (from GeneralLedger)

Total Cost = SUM(GeneralLedger[Cost])

Measure 3: Cost per Hour

Cost per Hour = 
DIVIDE(
    [Total Cost], 
    [Total Hours], 
    0
)

Step 4: Create Visuals

1. Go to the Report view.
2. Create charts or tables using the measures:
   • Drag Cost per Hour to a card visualization.
   • Create line charts or tables by dragging fields and measures to the canvas.

Step 5: Filter and Slicer Options

1. Add slicers to filter by date, employee, or other dimensions.
2. Test the relationships and ensure data integration works as expected.

Summary

By creating relationships and using DAX measures, you can seamlessly integrate the GeneralLedger and TimeKeeping tables in Power BI.

Proceed to the next unit for advanced DAX techniques and performance optimization. ```

DAX Measures Implementation for Integrating Data from General Ledger and Time Keeping Tables

Measure 1: Total Expenditures

Calculates the total expenditures from the General Ledger table.

Total Expenditures = SUM('General Ledger'[Expenditure Amount])

Measure 2: Total Hours

Calculates the total number of hours from the Time Keeping table.

Total Hours = SUM('Time Keeping'[Hours])

Measure 3: Average Cost Per Hour

Calculates the average cost per hour by dividing total expenditures by total hours.

Average Cost Per Hour = DIVIDE([Total Expenditures], [Total Hours])

Measure 4: Total Expenditures by Department

Calculates the total expenditures by department from the General Ledger table.

Total Expenditures by Department = 
    CALCULATE(
        SUM('General Ledger'[Expenditure Amount]),
        ALLEXCEPT('General Ledger', 'General Ledger'[Department])
    )

Measure 5: Total Hours by Project

Calculates the total number of hours by project from the Time Keeping table.

Total Hours by Project = 
    CALCULATE(
        SUM('Time Keeping'[Hours]),
        ALLEXCEPT('Time Keeping', 'Time Keeping'[Project])
    )

Measure 6: Cost Per Project

Calculates the total cost per project integrating both fact tables.

Cost Per Project = 
    CALCULATE(
        SUMX(
            'Time Keeping',
            'Time Keeping'[Hours] * [Average Cost Per Hour]
        ),
        ALLEXCEPT('Time Keeping', 'Time Keeping'[Project])
    )

Measure 7: Total Expenditures YTD

Calculates the year-to-date total expenditures from the General Ledger table.

Total Expenditures YTD = TOTALYTD(
    [Total Expenditures], 
    'Calendar'[Date]
)

Note: The Calendar table should have a relationship with both the General Ledger and Time Keeping tables and a continuous date column.

Measure 8: Total Hours YTD

Calculates the year-to-date total hours from the Time Keeping table.

Total Hours YTD = TOTALYTD(
    [Total Hours],
    'Calendar'[Date]
)

Measure 9: Cumulative Expenditures

Calculates the cumulative total expenditures.

Cumulative Expenditures = 
    CALCULATE(
        [Total Expenditures],
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] <= MAX('Calendar'[Date])
        )
    )

Measure 10: Cumulative Hours

Calculates the cumulative total hours.

Cumulative Hours = 
    CALCULATE(
        [Total Hours],
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] <= MAX('Calendar'[Date])
        )
    )

Measure 11: Budget Utilization Rate

Calculates the rate of budget utilization.

Budget Utilization Rate = 
    DIVIDE([Total Expenditures], 
           SUM('Budget'[Budget Amount]))

Measure 12: Staff Efficiency

Calculates the number of hours worked per dollar spent.

Staff Efficiency = DIVIDE([Total Hours], [Total Expenditures])

Use these DAX measures directly in your Power BI model to perform on-the-fly calculations and integrations between your General Ledger and Time Keeping tables. Ensure your data relationships are correctly set up for these measures to function accurately.

Step-by-Step Implementation of Visualizing Combined Data in Power BI

1. Define and Create Combined Measures:

Combine data from the general ledger and time keeping tables using DAX measures.

// Measure for Total Ledger Amount
TotalLedgerAmount = SUM('GeneralLedger'[Amount])

// Measure for Total Hours Worked
TotalHoursWorked = SUM('TimeKeeping'[Hours])

// Custom measure combining both tables' data
CombinedMeasure = 
    CALCULATE(
        [TotalLedgerAmount] / [TotalHoursWorked],
        REMOVEFILTERS('GeneralLedger'[Category])
    )

2. Create a Power BI Report

• Place a new visual to represent the combined measure.
• Add slicers and filters as required.

3. Add Visual Elements

Combined Measure Card

CombinedMeasureCard = 
CARD (
    [CombinedMeasure]
)

Table Visual

• Add columns from 'GeneralLedger' and 'TimeKeeping' to compare.

Line Chart

• Place 'Date' on the X-axis.
• Place TotalLedgerAmount and TotalHoursWorked on the Y-axis.
• Use dual-axis if needed.

4. Create Visualization Components

// Date Table - Use for filtering and time intelligence functions
DateTable = CALENDAR(MIN('TimeKeeping'[Date]), MAX('TimeKeeping'[Date]))

// Link DateTable to 'GeneralLedger' and 'TimeKeeping' tables via relationships.

Cross-Filter Established Relationships

Ensure the relationships between 'GeneralLedger', 'TimeKeeping', and 'DateTable' are properly filtered.

5. Publish and Share Report

• Publish to Power BI Service.
• Share dashboard with relevant stakeholders.

Conclusion

This practical implementation integrates data from two fact tables in Power BI using DAX measures, facilitating a seamless and insightful data visualization experience.