Power BI for data analysts

99 levels in 12 chapters

Learn Power BI the way analysts use it: Power Query steps, a star-schema model, DAX measures checked cell by cell in a live matrix, visuals, dashboards and row-level security.

By the end you can shape and model the data, write CALCULATE, time-intelligence and iterator measures that survive any filter, and design a report people can trust and act on. Every level is open; the path only suggests an order.

Chapter 1 · 8 levels

Business intelligence and Power BI

8 lanterns to light

  1. What happened, why, what next, what to do
  2. Prepare, model, visualize, analyze, manage
  3. Desktop, Service and Mobile, reports and dashboards
  4. Report, Table and Model view, and the three panes
  5. Your first measure, units ordered
  6. The settings to change on day one
  7. Import, DirectQuery or a live connection?
  8. Boss Boss: three measures for the channel review

Chapter 2 · 8 levels

Getting and shaping data in Power Query

8 lanterns to light

  1. Import only what the report needs
  2. Fix types and messy text in a customer export
  3. Split a SKU into the parts you can join on
  4. Band orders by size and add the month
  5. Turn a month-per-column report into rows
  6. Which join kind does the merge need?
  7. Merge in the category, then group the returns
  8. Boss Boss: two messy exports, one clean table

Chapter 3 · 9 levels

Data modelling and the star schema

9 lanterns to light

  1. Facts and dimensions
  2. The grain of a table
  3. Your first relationship
  4. Relate order lines, orders and customers
  5. Star, snowflake or spaghetti?
  6. Connect and mark the Date table
  7. An inactive relationship for signup dates
  8. Hierarchies and drill-down
  9. Boss Boss: build the sales star schema

Chapter 4 · 9 levels

DAX basics: columns and measures

9 lanterns to light

  1. A price band column with IF
  2. An order month column with MONTH
  3. Quantity bands with SWITCH(TRUE())
  4. Count customers with DISTINCTCOUNT
  5. Price range with MIN and MAX
  6. Column or measure?
  7. Average order value with DIVIDE
  8. Line cost with RELATED
  9. Boss Boss: the category margin review

Chapter 5 · 9 levels

CALCULATE and filter context

9 lanterns to light

  1. What does this cell filter?
  2. Completed sales with CALCULATE
  3. Several filters at once, and IN
  4. Filter by a measure with FILTER
  5. Share of total with ALL
  6. Share within the category with ALLEXCEPT
  7. Fix it: every product is high demand
  8. Keep the row's filter with KEEPFILTERS
  9. Boss Boss: the business customer review

Chapter 6 · 9 levels

Time intelligence

9 lanterns to light

  1. A weekend column on the Date table
  2. Year-to-date sales with TOTALYTD
  3. Month-to-date by day with DATESMTD
  4. Last year's sales with SAMEPERIODLASTYEAR
  5. Year-over-year growth with VAR and DIVIDE
  6. Previous month and MoM % with DATEADD
  7. Why is my YTD just the month?
  8. Year-to-date last year
  9. Boss Boss: the app channel's year-over-year report

Chapter 7 · 9 levels

Advanced DAX

9 lanterns to light

  1. Revenue line by line with SUMX
  2. Average order value with AVERAGEX
  3. A weighted margin with SUMX and RELATED
  4. Rank categories with RANKX
  5. Sales of the top five products with TOPN
  6. Share of the selection with ALLSELECTED
  7. New customers by signup date with USERELATIONSHIP
  8. Fix a measure with VAR and RETURN
  9. Boss Boss: the holiday channel review

Chapter 8 · 7 levels

Visuals that answer the question

7 lanterns to light

  1. Choose the visual from the question
  2. Table or matrix?
  3. Card or KPI visual?
  4. Sales against a target table
  5. Fix the chart before it ships
  6. Custom visuals from AppSource, safely
  7. Boss Boss: the numbers behind a monthly KPI

Chapter 9 · 8 levels

Interactivity

8 lanterns to light

  1. What a slicer does to a measure
  2. Visual, page or report filter?
  3. Cross-filter, cross-highlight and Edit interactions
  4. Top 3 products as a measure
  5. A title that follows the slicer
  6. Default tooltips and report-page tooltips
  7. Bookmarks and buttons
  8. Boss Boss: a channel page that survives every click

Chapter 10 · 7 levels

Dashboards and storytelling

7 lanterns to light

  1. What is this dashboard for?
  2. Which KPIs for which audience?
  3. Report, dashboard or analysis?
  4. Strategic, operational or analytical?
  5. Lay out the page from summary to detail
  6. Context, conflict, insight, recommendation
  7. Boss Boss: the KPIs of the executive page

Chapter 11 · 8 levels

Power BI Service, sharing and security

8 lanterns to light

  1. Publish a report and pick the licences
  2. Give each colleague the right workspace role
  3. Workspace, app or dashboard?
  4. A row-level security role for the West
  5. One dynamic rule for every territory
  6. Who row-level security applies to
  7. Scheduled refresh, gateways and outside guests
  8. Boss Boss: secure the B2B sales report

Chapter 12 · 8 levels

Optimising Power BI reports

8 lanterns to light

  1. The storage engine and the formula engine
  2. Fix the slow measure
  3. Column types, cardinality and compression
  4. A lean import that folds
  5. Import, DirectQuery or a hybrid?
  6. Take the traps out of the relationships
  7. Work it out once with VAR
  8. Boss Boss: rescue the slow discount report

Megan Collins · Head of Sales

"App revenue fell from about $284K in Q2 to $217K in Q3 this year, but the number of app orders barely moved: 406, then 405. Before Thursday's call with the app team I need to understand what's behind the drop. Smaller baskets? Fewer big furniture orders? Something else?"

Light the next lantern

Cookie settings

Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.