Skip to content

Power BI — Data Modelling, DAX & Reporting

Last reviewed: 2026-09-17

Purpose: Reference for Microsoft Power BI — connecting to data, shaping it with Power Query, modelling relationships, writing DAX measures, designing reports and publishing them to the Power BI Service.

Overview

Power BI is Microsoft's analytics platform for business intelligence. The workflow has four stages:

  1. Ingest & transform — connect to sources and shape data with Power Query (M language)
  2. Model — define relationships and a star schema over a tabular (VertiPaq) engine
  3. Measure — write DAX calculations over that model
  4. Publish — author reports in Power BI Desktop and share them through the Power BI Service

Topics Covered

Data Preparation & Modelling

  • Power Query (M language) for data transformation
  • Data model relationships (star schema, snowflake)
  • Calculated columns and measures
  • Row context vs filter context

DAX (Data Analysis Expressions)

  • CALCULATE, FILTER, ALL, VALUES
  • Time intelligence (TOTALYTD, SAMEPERIODLASTYEAR, DATEADD)
  • Iterator functions (SUMX, AVERAGEX, RANKX)
  • Context transition and evaluation context

Report Design

  • Visualisation selection (charts, tables, maps, KPIs, slicers)
  • Drill-through and drill-down
  • Bookmarks and buttons for navigation
  • Custom visuals from AppSource
  • Accessibility: alt text, tab order and contrast

Publishing & Sharing

  • Power BI Service workspace management
  • Dashboard pinning and apps
  • Scheduled refresh and gateway configuration
  • Row-level security (RLS)
  • Embedding in custom applications

Modelling Cheat Sheet

Concept What it means Typical use
Calculated column Computed per row at refresh time, stored in the model Slicers, relationships, row attributes
Measure Computed at query time from the filter context Aggregations, KPIs
Row context The "current row" while a column is evaluated Iterators (SUMX, FILTER)
Filter context The filters applied by visuals, slicers or CALCULATE What a measure aggregates over
Context transition Row context converted to filter context by CALCULATE Filtering by the current row inside an iterator

Model with a star schema: dimension tables (date, product, customer) filtering narrow fact tables. Bidirectional and many-to-many relationships are supported but should be the exception, not the default.

DAX Patterns

-- Base measure
Revenue = SUM(Sales[Revenue])

-- Filter context modified by CALCULATE
Revenue Europe = CALCULATE([Revenue], Region[RegionName] = "Europe")

-- Time intelligence requires a marked, contiguous date table
Revenue YTD = TOTALYTD([Revenue], 'Date'[Date])
Revenue PY  = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
Revenue YoY % = DIVIDE([Revenue] - [Revenue PY], [Revenue PY])

-- Iterators: row-by-row evaluation, then aggregation
Order Value = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
Best Product Rank = RANKX(ALL(Product[ProductName]), [Revenue])

-- Ignore slicer filters with ALL / REMOVEFILTERS
Share of Total = DIVIDE([Revenue], CALCULATE([Revenue], ALL(Sales)))

Rule of thumb: prefer measures over calculated columns — measures react to whatever the report filters and keep the model small.

Row-Level Security

-- Filter expression on a table, evaluated as the signed-in user
[Region] = LOOKUPVALUE(
    UserRegions[Region],
    UserRegions[Email],
    USERPRINCIPALNAME()
)

Roles are defined in Desktop (Modeling → Manage roles), tested with View as role, and then assigned to users or groups in the Power BI Service. Members of a workspace admin group bypass RLS — design access accordingly.

Refresh & Gateway Notes

Mode Storage Freshness Trade-off
Import Data copied into the model Scheduled refresh Fastest queries, data is stale between refreshes
DirectQuery None — queries pushed to source Live Always current, bounded by source performance
Composite Mixed, plus aggregation tables Both Best of both at the cost of complexity
  • On-premises sources need the on-premises data gateway for scheduled refresh; gateways are administered per workspace.
  • Incremental refresh (with RangeStart / RangeEnd parameters) keeps large fact tables refreshable within time limits.
  • Refresh failures are visible in the Service under Semantic model → Refresh history, and can trigger alerts.

Report Design Essentials

  • One question per visual; the title should state the insight, not the measure name.
  • Put the most important KPI top-left; arrange detail below or behind drill-through.
  • Use bookmarks for guided navigation and buttons for reset/back actions.
  • Keep the colour palette small and use it consistently; never encode meaning in colour alone.
  • Test with keyboard-only navigation and a screen reader before publishing widely.

Resources

To-do

Status: incomplete. Still to write:

  • Add a Power Query example in the M language (a transform edited in the Advanced Editor) — "Power Query (M language) for data transformation" is listed under Topics Covered but the article contains no M code at all.
  • Write the publishing and sharing content the "Publishing & Sharing" list promises: Power BI Service workspaces, dashboard pinning and apps (audiences, update schedules) — only refresh/gateway and RLS are actually covered today.
  • Cover embedding in custom applications (Publish to web, secure embed, embed tokens) — listed under Publishing & Sharing, absent from the body.
  • Demonstrate the report-design items that are only named: adding a custom visual from AppSource, and setting up drill-through (drill-through fields and the drill-through page) with a short worked example.