Import business records into Power BI Desktop, model relationships using a star schema, write custom DAX time-intelligence metrics, and design interactive BI dashboards.
Domain / Environment
Business Intelligence / Windows Host
Difficulty
Intermediate (3/5)
Course Module
BI with Power BI
Deliverables
Power BI Workbook (.pbix) & Dashboard Layout
1. System Architecture & Relational Model
The diagram below displays the Power BI data model. It uses a star schema model, linking dimension tables (Customers and Calendar) to a central transaction fact table (Sales) using one-to-many (1:*) relationships.
Open Power BI Desktop on your Windows host and load the dimension and fact tables.
Click Home tab in ribbon -> Click "Get Data" icon -> Select "Text/CSV" -> Select customer_records.csv -> Click "Transform Data" to open Power Query
This loads data tables into Power Query for transformation. Repeat these steps for `sales_facts.csv` and `calendar_dim.csv`.
STEP 2
Transform Data Columns in Power Query
Clean table columns and set data types in Power Query Editor.
Select sales_facts table on left -> Right-click CustomerKey column header -> Click "Change Type" -> Select "Whole Number" -> Click "Close & Apply" in top-left Home tab
Setting numeric data types enables database key joins and fast row lookups.
STEP 3
Model Star Schema Relationships
Define table relationships using the Model View dashboard layout.
Click "Model View" icon (third icon on far-left vertical sidebar) -> Drag CustomerKey from Dim_Customers -> Drop onto CustomerKey column inside Fact_Sales
This creates a one-to-many (1:*) relationship, linking customer profiles to transaction rows. Drag `DateKey` to `OrderDateKey` to link the calendar table.
STEP 4
Create Custom DAX Measures
Author KPI calculations in the Report View dashboard.
Click "Report View" icon (first icon on vertical sidebar) -> Click Fact_Sales table on right -> Click "New Measure" in Table Tools ribbon -> Paste DAX code from Part 2
This compiles DAX formulas, adding key metrics (like Total Revenue and YoY growth) to your data model.
STEP 5
Build Interactive UI Visuals
Construct visual dashboard elements (KPI cards, trends, and charts) to display metrics.
Click "Card" icon in Visualizations panel -> Drag Total Revenue measure to Fields -> Resize card to 1.5 inches height -> Place in top header area
This adds a KPI card for total revenue to the dashboard page. Add a Line Chart (Revenue by Year/Month) and a Bar Chart (Revenue by Country) to complete the layout.
3. Dashboard Visual Layout (Wireframe Design)
The visual layout below displays the target dashboard structure. Slicers sit in the left sidebar, KPI cards align along the top header, and line/bar charts occupy the main dashboard area.
4. Part 2: Complete Deliverable Assets & Production Templates
To build the dashboard, we need the raw sales dataset files and the DAX formulas. Below is an explanation of the DAX formulas, followed by the sample CSV datasets.
DAX Code Formulation
Measure 1
Total Revenue Calculation
Calculates total sales by multiplying unit price by quantity sold across all transactions.
Total Revenue = SUMX(Fact_Sales, Fact_Sales[Quantity] * Fact_Sales[UnitPrice])
This uses `SUMX` to calculate sales row-by-row and sum the results.
Measure 2
Prior Year Revenue (Time Intelligence)
Calculates revenue for the same period in the previous year to enable year-over-year comparisons.
Prior Year Revenue = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Dim_Calendar[Date]))
This shifts the date context back by one year using `SAMEPERIODLASTYEAR` to calculate prior year sales.
Measure 3
YoY Revenue Growth
Calculates the percentage change in revenue compared to the previous year.
YoY Revenue Growth = DIVIDE([Total Revenue] - [Prior Year Revenue], [Prior Year Revenue], 0)
This uses `DIVIDE` to calculate year-over-year growth, return 0 if the denominator is null, and formats the result as a percentage.
Production templates
1. Customers Dimension (Save as customer_records.csv):