Power BI

Power BI is Microsoft's end-to-end BI tool: load data with Power Query, model it in the data view, write DAX measures, and publish interactive reports to the Power BI Service. If your company runs on Microsoft 365, this is probably the dashboard you'll be asked to build.

What Power BI Actually Is

Three apps that work together:

Component	Job
Power BI Desktop	build reports & models on your laptop
Power BI Service (web)	share, schedule refresh, manage permissions
Power BI Mobile	view on phone/tablet

A .pbix file is everything — queries, model, measures, visuals — in one bundle.

The 4 Layers Inside Power BI Desktop

1. Power Query (Get Data → Transform Data) — connect, clean, shape. Language: M. 2. Model view — define table relationships, mark date table. 3. Data view — write calculated columns / measures in DAX. 4. Report view — drag visuals onto the canvas.

Most beginner pain comes from doing the wrong job in the wrong layer (e.g. cleaning data with DAX instead of Power Query).

Beginner Mistakes to Skip

1. Cleaning in DAX instead of Power Query. Trim, split, type-cast belong in Power Query — it runs once at refresh. 2. No date table. Time intelligence (SAMEPERIODLASTYEAR, DATESYTD) needs a continuous date table marked as such. 3. Bidirectional or many-to-many relationships everywhere. Performance + ambiguity disaster. Default to one-to-many, single direction. 4. Calculated columns when a measure would do. Columns inflate the file. Measures are computed on demand. 5. One giant table (flat-table thinking). Build a star schema instead: one fact, many dimensions. 6. Auto-date hierarchies on every column. Bloats the model. Turn it off and use your own date table.

Intermediate: Power Query (M) Essentials

Click-driven, but every click writes M code in the formula bar:

let
    Source = Csv.Document(File.Contents("sales.csv")),
    Promoted = Table.PromoteHeaders(Source),
    Typed = Table.TransformColumnTypes(Promoted, {{"Date", type date}, {"Amount", Currency.Type}}),
    Cleaned = Table.SelectRows(Typed, each [Amount] <> null and [Amount] > 0)
in
    Cleaned

Key idea: Power Query is applied steps — every transformation is recorded and replayed at every refresh. Reproducible, editable, version-friendly.

Intermediate: Star Schema Modelling

• Fact table — events / measurements (Sales, Orders, Sessions). Long & narrow.
• Dimension tables — descriptive context (Date, Product, Customer, Region). Short & wide.
• Connect each dimension to the fact with one-to-many relationships.

This isn't optional — DAX is *designed* around star schemas. Every query gets faster and every measure gets simpler.

Intermediate: DAX — Two Contexts

DAX is the formula language. The hardest concept is two evaluation contexts:

• Row context — "this current row". Created by calculated columns and iterators (SUMX, FILTER, AVERAGEX).
• Filter context — "which rows are visible right now". Created by slicers, page filters, and the visual being rendered.

Total Sales = SUM(Sales[Amount])                        -- aggregates within filter context
Revenue Per Unit = SUMX(Sales, Sales[Qty] * Sales[Price])  -- iterates row context

A visual cell value = measure evaluated under that cell's filter context.

Intermediate: CALCULATE — The Most Important Function

CALCULATE *modifies* filter context. It's the verb that powers 80% of business measures:

Sales LY        = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Sales Online    = CALCULATE([Total Sales], Sales[Channel] = "Online")
Pct of Total    = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales)))
YoY %           = DIVIDE([Total Sales] - [Sales LY], [Sales LY])

ALL removes filters; KEEPFILTERS adds without overriding; REMOVEFILTERS is the modern alias for ALL.

Intermediate: Time Intelligence

Given a proper date table, DAX makes calendar math one-liners:

Sales YTD     = TOTALYTD([Total Sales], 'Date'[Date])
Sales MTD     = TOTALMTD([Total Sales], 'Date'[Date])
Sales 12M     = CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH))

Always mark your date table: Modeling → Mark as Date Table → Date column.

Advanced: Variables, DIVIDE & Best Practices

Profit Margin =
VAR Revenue = SUM(Sales[Amount])
VAR Cost    = SUM(Sales[Cost])
VAR Profit  = Revenue - Cost
RETURN DIVIDE(Profit, Revenue)

• Use VAR for clarity and to evaluate sub-expressions once.
• Always use DIVIDE(num, den) instead of / — it returns BLANK on divide-by-zero.
• Prefix measures (e.g. # Sales, % Margin) so they sort apart from columns.

Advanced: Performance — Storage Modes & VertiPaq

Power BI's column store (VertiPaq) compresses columns aggressively. Tips:

• High cardinality columns (timestamps, GUIDs) cost the most. Split datetime into date + time.
• Disable "Auto date/time" globally; use one shared date table.
• Prefer Import mode over DirectQuery unless data freshness demands it.
• For huge fact tables, use Composite models + aggregations so summary visuals hit a small aggregated table.
• Profile with Performance Analyzer in Desktop and DAX Studio for hot measures.

Advanced: Row-Level Security (RLS)

-- Role: Region Manager
[Region] = USERPRINCIPALNAME()  -- maps logged-in email to a Region row

Define roles in Modeling → Manage Roles, assign users in the Service. Each user sees only their slice — same dashboard, different data.

Advanced: Deployment & Refresh

• Publish .pbix → Workspace → App.
• Datasets decouple from reports — many reports can share one dataset.
• Schedule refresh (8/day on Pro, 48/day on Premium) and configure an on-prem data gateway for local sources.
• Use deployment pipelines (Dev → Test → Prod) for governed changes.

Practice Path

1. Load a sales CSV with Power Query — type the columns, drop nulls, build a date table with Calendar.Auto or DAX CALENDAR. 2. Build a star schema: Sales fact + Date / Product / Customer dimensions. 3. Write 5 measures: Total Sales, Sales LY, YoY %, % of Total, Profit Margin (with VAR + DIVIDE). 4. Build a 1-page report: KPI cards on top, line chart of Sales vs LY, bar by category, slicer by region. Publish to the Service and share.