Skip to content

Power BI Cheat Sheet

Quick reference for Power BI Desktop — data loading, DAX measures, and report design.

Getting Started

Power BI Desktop workflow: 1. Get Data → connect to your source (Excel, SQL, SharePoint, web) 2. Power Query Editor → clean and transform data 3. Model view → set relationships between tables 4. Report view → build visuals 5. Publish → upload to Power BI Service for sharing

Power Query (Transform Step)

Task How
Remove blank rows Home → Remove Rows → Remove Blank Rows
Change column type Click column type icon (ABC/123) or Transform → Data Type
Split column Transform → Split Column → By Delimiter
Unpivot (wide → long) Select columns → Transform → Unpivot Columns
Pivot (long → wide) Transform → Pivot Column
Merge tables (JOIN) Home → Merge Queries → select join type
Group by (aggregate) Transform → Group By
Add conditional column Add Column → Conditional Column
Refresh Close & Apply → Refresh All

Important: column names set in Power Query become your field names everywhere. Name them clearly.

Data Model

Relationships: - Primary key (1) → Foreign key (many) — always this direction - Active vs inactive relationships — only one active at a time per table pair - Star schema preferred: fact table in centre, dimension tables around it

dim_customers ──┐
dim_products  ──┤── fact_orders ──── dim_date
dim_regions   ──┘

DAX Fundamentals

Calculated column (row context — runs at refresh): 

order_size = IF(fact_orders[total] > 300, "Large", IF(fact_orders[total] > 100, "Medium", "Small"))

Measure (filter context — runs when visual renders): 

Total Revenue = SUM(fact_orders[total])
Order Count = COUNTROWS(fact_orders)
Avg Order = AVERAGE(fact_orders[total])

DAX Patterns

Basic aggregations

Total Revenue = SUM(orders[total])
Completed Revenue = CALCULATE(SUM(orders[total]), orders[status] = "completed")
Unique Customers = DISTINCTCOUNT(orders[customer_id])

% of total

Revenue % of Total =
DIVIDE(
    SUM(orders[total]),
    CALCULATE(SUM(orders[total]), ALL(orders[category])),
    0
)

Year-over-year

Revenue Last Year = CALCULATE(SUM(orders[total]), SAMEPERIODLASTYEAR('Date'[Date]))
YoY Growth % = DIVIDE([Total Revenue] - [Revenue Last Year], [Revenue Last Year], 0)

Running total

Revenue YTD = TOTALYTD(SUM(orders[total]), 'Date'[Date])

Previous period

Revenue Last Month = CALCULATE(SUM(orders[total]), PREVIOUSMONTH('Date'[Date]))
MoM Change = [Total Revenue] - [Revenue Last Month]
MoM % = DIVIDE([MoM Change], [Revenue Last Month], 0)

Rank

Revenue Rank = RANKX(ALL(dim_products[product_name]), [Total Revenue], , DESC, Dense)

DIVIDE — safe division (avoids divide-by-zero)

Conversion Rate = DIVIDE([Purchases], [Visits], 0)  -- returns 0 instead of error

Key DAX Functions

Function Purpose
SUM / AVERAGE / MAX / MIN Basic aggregation
COUNT / COUNTA / COUNTROWS Count values, non-blank, or rows
DISTINCTCOUNT Count unique values
CALCULATE(expr, filter) Modify filter context
ALL(table/column) Remove all filters (for % of total)
FILTER(table, condition) Return subset of table
RELATED(column) Access column from related table
DIVIDE(numerator, denominator, alternate) Safe division
SWITCH(expr, val1, res1, val2, res2, else) Multi-value conditional
DATEADD(dates, n, MONTH/YEAR) Shift dates
SAMEPERIODLASTYEAR(dates) Same period one year ago
TOTALYTD(expr, dates) Year-to-date running total
USERELATIONSHIP(col1, col2) Activate an inactive relationship
ISINSCOPE(column) Check which level of hierarchy is active

Report Design

Visuals quick-reference:

Question Visual
Trend over time Line chart or area chart
Comparison across categories Bar chart or column chart
Part of whole Pie chart (max 5 slices) or donut chart
Distribution Histogram or box plot
Relationship Scatter chart
Tabular detail Table or matrix
Single number Card
KPI with target KPI visual
Geography Map or filled map

Slicers: give users filter control without cluttering the visual. Use for: date, region, category, status.

Drill-through: right-click on a data point → drill through to a detail page. Set up in Visualisations → Drill Through.

Performance Tips

  • Use Import mode for historical data (faster); Direct Query only for real-time needs
  • Avoid calculated columns when a measure will do — columns use model memory
  • Add a dedicated Date dimension table — gives you time intelligence functions
  • Turn off "Auto date/time" in Options if you have your own date table
  • Use Incremental refresh for large tables — only loads new/changed data
  • Check Performance Analyzer (View menu) to find slow visuals
  • Avoid bi-directional relationships — they create ambiguity and slow queries