Create a calculated column in order-details to get line totals using the related products table:
LineTotal = RELATED(products[unitPrice]) * order-details[quantity] * (1 - order-details[discount])
From basic calculations to cleaner data
Session 2 introduces beginner DAX measures, Power Query transformations, and practical analysis features used by business teams in day-to-day reporting.
Expectation reminder: this course is an introduction to get learners started, not advanced mastery. For comprehensive learning, the DataCamp Power BI Fundamentals Track (17 hours) provides complete coverage beyond these two sessions.
Part 1: Basic DAX Measures
Use this section as a live demo. Each formula includes a copy button so learners can paste directly into Power BI.
Total Sales = SUM(order-details[LineTotal])
Total Orders = DISTINCTCOUNT(orders[orderID])
Avg Order Value = DIVIDE([Total Sales], [Total Orders], 0)
Customer Count = DISTINCTCOUNT(orders[customerID])
Beverages Sales =
CALCULATE(
[Total Sales],
categories[categoryName] = "Beverages"
)
Germany Sales =
CALCULATE(
[Total Sales],
customers[country] = "Germany"
)
Talking point: use measures for dynamic report totals and calculated columns for row-level logic.
Part 2: Power Query Transforms (Beginner)
Open dirty CSV files via Transform Data and run these guided clean-up blocks.
- Remove top rows (title rows + blank rows).
- Use first row as headers.
- Set data types: Salary = Whole Number, StartDate = Date.
- Text functions: Capitalize each word, Trim, Replace double spaces.
- Replace invalid values: N/A and PENDING to null.
- Remove top rows and promote headers.
- Replace "£" symbol in Sale Amount and set Decimal Number type.
- Replace invalid entries (REFUND, TBC) with null.
- Normalize text casing for Product Sold, Payment Method, and Region.
- Demonstrate Fill Down on suitable missing fields.
- Remove top noise rows, promote headers.
- Close & Apply all three queries.
- Create relationships: Employees[EmployeeID] → Sales[EmployeeID].
- Create relationship: Departments[Department] → Employees[Department].
- Always verify data type icons in each column.
- Numbers stored as text cannot aggregate correctly in visuals.
- Dates must be proper Date type for slicing and trends.
- Replace Values for symbols and bad values.
- Trim + Clean for spacing and hidden characters.
- Capitalize Each Word for consistent presentation.
- Remove Top Rows for pre-header report titles.
- Remove Blank Rows to stabilize joins.
- Remove unused columns early to simplify the model.
- Applied Steps are your reusable data-cleaning script.
- Refresh replays the same transformations for new files.
- Use profiling (column quality/distribution) to spot anomalies.
Part 3: Analysis Features
- Create Region + Category slicers and demonstrate combined filtering.
- Use Edit Interactions to control filter/highlight behavior.
- Explain why slicers are a must-have for business stakeholders.
- Create a Customer Detail page and add CustomerName as drillthrough field.
- Right-click from summary visuals to navigate to detail context.
- Use back button to return to the summary page.
- Apply color scales to matrix cells for fast interpretation.
- Add extra tooltip measures for richer context on hover.
- Briefly show bookmarks for guided navigation.
Further Learning & Next Steps
Final reminder: this bootcamp is only an introduction. It gives learners a practical start but they will need deeper study to master DAX and Power Query.
| Learning Path | Goal | Suggested Resource |
|---|---|---|
| DAX Mastery | Context transition, time intelligence, optimization | SQLBI learning tracks + DAX Guide practice |
| Power Query Mastery | Advanced shaping, query folding, M functions | Microsoft Learn Power Query path + hands-on projects |
| Power BI Delivery | Publishing, sharing, governance, refresh | Microsoft Learn service administration modules |
| Structured Learning | Complete Power BI fundamentals (17 hours) | DataCamp Power BI Fundamentals Track |
| Practical Practice | Portfolio-quality dashboards and storytelling | Repeat this course workflow on new datasets |