Power BI lessons

What is Power BI?

Meet Power BI Desktop: the ribbon, the three views and the Data pane.

Explore your data in Table view

Read rows and columns before you build anything.

Your first visual: a card

Pick a visual, add a field, and read your first KPI.

PracticeFind the numbers

Table view, cards and the Data pane, on new questions.

Skill checkGetting started

Views, tables, fields and cards in one short test.

Compare categories with bar charts

Use bars to see which country and product win.

Trends with column and line charts

Show values over time and pick the right chart for the job.

Parts of a whole: pie charts

Show how a total splits into a few parts, and know when not to.

Exact numbers with table visuals

Show precise values per category, with a total row.

PracticePick and build the chart

Four requests, four visuals. You choose.

The matrix: rows, columns and totals

The visual analysts use most: a cross-table with totals, like a pivot table.

PracticeBuild a matrix

Segment by product, read the cells and the totals.

Sum, average and count

Change how Power BI summarizes a field: implicit measures.

Slicers and cross-filtering

Filter a whole page with a slicer, and by clicking a chart.

Format visuals: titles, colors, labels

Turn a default chart into one a manager reads in five seconds.

PracticeFilter a page

Slicers and cross-filtering on a ready-made page.

Skill checkVisuals

Charts, aggregations, slicers and formatting in one test.

Tables and relationships

Read Model view, understand filter direction and a star schema.

Facts and dimensions

Tell event tables from description tables, the base of every good model.

Create a relationship

Connect a dimension to the fact table, then use it in a visual.

RELATED and RELATEDTABLE

Use relationships in calculated columns, in both directions.

How filters flow

Slicers on a dimension filter the facts, not the other way around.

Good model design

Cardinality, filter direction and why star schemas win.

PracticeConnect and use a dimension

Build the relationship yourself, then answer questions through it.

Skill checkData model

Relationships, RELATED, filter flow and model design in one test.

Your first query

Open a raw CSV export, fix its headers and types, and see the M code behind every click.

Clean an export: rows, types, errors

Remove junk rows and duplicates, set types and deal with errors, the way you would on the job.

Fix text: trim, case, split, replace

Turn " canada", "GERMANY " and "Smith, Anna" into values a report can group by.

Append and merge queries

Stack files from two stores, then bring in product details with a merge.

Unpivot wide spreadsheets

Turn one-column-per-month sheets into tables Power BI can filter and chart.

Fix report-style Excel sheets

Title rows, merged-looking cells and subtotals: turn a printed budget into a real table.

PracticeShape a sales table

Filter, add a column, set its type and summarize, from plain requests.

Skill checkPower Query

Clean a raw export from start to finish.

Your first measure: SUM

Write DAX measures and use them in visuals.

AVERAGE, MIN and MAX

Find typical, smallest and largest values.

PracticeSUM, AVERAGE, MIN, MAX

Write five measures from plain requests.

Counting with COUNTROWS and DISTINCTCOUNT

Count rows and unique values, and meet filter context.

COUNT and COUNTA

Count values in a column, and know which count to use.

Profit margin with DIVIDE

Divide safely and compare margins across products.

ROUND: tidy numbers

Round results to whole numbers, thousands or two decimals.

IF: make a decision

Return one value or another depending on a test.

AND, OR and NOT: combine conditions

Test two things at once with && and ||, the way every real IF ends up.

SWITCH: many cases

Sort rows into bands with SWITCH(TRUE(), ...).

Blanks: ISBLANK and COALESCE

Why some rows disappear from your visuals, and how to show a zero instead.

PracticeWrite the measure

Plain business questions; you write the DAX.

Calculated columns and RELATED

Add a column that is calculated row by row, and look up values in another table.

PracticeCalculated columns

Row-by-row formulas from plain requests.

Skill checkDAX basics

Measures, counts, DIVIDE, IF and columns in one test.

CALCULATE: change the filters

Use CALCULATE to compute a measure under different filters.

CALCULATE with several filters

AND, OR and IN: combine filters the right way.

CALCULATE inside a visual

See which filters CALCULATE keeps and which it replaces.

ALL and percent of total

Remove filters with ALL / REMOVEFILTERS to calculate shares.

PracticeCALCULATE

Filtered measures and a share of total, from plain requests.

REMOVEFILTERS and share of a group

Clear one filter to compare a row with its whole group.

FILTER and VALUES

Filter with conditions on measures, and count what qualifies.

ALLEXCEPT, ALLSELECTED and KEEPFILTERS

Control exactly which filters CALCULATE keeps.

Dynamic titles with SELECTEDVALUE

Make text react to what the user selects.

Fix wrong totals: HASONEVALUE and ISINSCOPE

Totals are calculated, not added up. Control what the Total row shows.

Skill checkCALCULATE

Filters, removing filters and shares in one test.

SUMX and the X functions

Calculate row by row inside a measure.

AVERAGEX, MINX and MAXX

Average, smallest and largest of an expression, row by row.

Iterate over groups with VALUES

Average per country, best country, countries above a target.

Variables, IF and SWITCH

Write readable measures with VAR and add business logic.

PracticeIterators

Row-by-row totals and per-group results, from plain requests.

Skill checkIterators

Row context, X functions and per-group results in one test.

Date tables

Why every model needs a proper date table.

Year-to-date with TOTALYTD

Running totals that restart every year.

Quarter-to-date and month-to-date

TOTALQTD and DATESQTD: the numbers every finance team reports.

Compare with last year

SAMEPERIODLASTYEAR and year-over-year growth.

Previous month and DATEADD

Shift time by months, quarters or years.

PracticeYTD and last year

Year-to-date, same period last year and growth, from plain requests.

Year-over-year growth, done right

Build YoY %, then catch the partial-year trap that makes growth look huge.

Skill checkTime intelligence

Date tables, YTD and previous periods in one test.

Rank with RANKX

Rank countries and products by any measure.

Top N and lists

TOPN for "top 2" totals, CONCATENATEX for text lists.

Text functions and FORMAT

Build codes, labels and nicely formatted numbers.

Text functions: LEFT, LEN, SEARCH, SUBSTITUTE

Clean labels and build codes with DAX text functions.

Conditional formatting

Color cells and bars by their values: gradients, rules and color measures.

PracticeRank and label

Ranks, the last place and a formatted label, from plain requests.

Skill checkRanking and formatting

RANKX, TOPN and formatting in one test.

Publish and share

From Desktop to the Power BI service: workspaces, reports, dashboards, apps.

Refresh and gateways

Keep published data up to date.

Row-level security

Show each reader only their own rows.

Row-level security, hands-on

Write a role filter, see what each manager sees, and make it dynamic.

Skill checkShare and secure

Publishing, refresh and security in one test.

Context transition, for real

Predict exactly when a row becomes a filter, and use it on purpose.

Virtual tables: ADDCOLUMNS and SUMMARIZE

Build tables inside a measure, then iterate over them.

Running totals and rolling windows

Cumulative totals that never reset, and last-3-months windows.

Pareto and ABC classification

Cumulative share and A/B/C classes, a classic sales analysis.

Inactive relationships: USERELATIONSHIP

Report by ship date as well as order date, from one Dates table.

Everyday functions: dates, lookups, text

DATEDIFF, LOOKUPVALUE, QUARTER, EOMONTH and friends, on real questions.

Budget vs actual: TREATAS

Compare sales with a targets table that has no relationship, the way finance teams need it.

What-if parameters

Let managers ask "what if we gave a 10% discount?" with a slicer.

Window functions: OFFSET, WINDOW, INDEX

Previous row, moving windows and first/last rows, the modern way.

Statistics: median, percentiles, spread

Describe data honestly when a few big orders distort the average.

Average of ratios vs ratio of sums

The margin bug that reaches board decks: why two "correct" formulas disagree, and which one to trust.

Top N and the rest

The top 3 countries, everything else, and their share: the slide every manager asks for.

Compare with the average

Above or below average, by how much, and how many: benchmarking that works in any visual.

Make slow reports fast

Find the slow part, then fix the model and the DAX.

Write faster DAX

Variables and simple filters: the two habits that make measures fast and readable.

Calculation groups and field parameters

Stop copying measures: one set of time calculations for every measure.

Incremental refresh and deployment

Refresh only what changed, and ship safely from dev to production.

Semi-additive measures: balances and stock

Some numbers must not be added up over time.

Custom and conditional columns

Write M formulas, avoid the classic type errors, and build size bands.

Group, sort and rank

Build a country summary with several outputs, then number it.

Broken steps and robust queries

Fix a refresh that broke overnight, catch bad values with try … otherwise, and plan for query folding.

Project: the monthly sales file

Take a raw export from the brief to a clean, loaded Sales table, with less help along the way.