Power BI cheat sheets

A compact sheet per level (about two printed pages each): the patterns, formulas and checklists you would otherwise look up. Choose a level and press Print. Your browser's print dialog can also Save as PDF (on iPhone/iPad: Share → Print, then pinch out on the preview to save it).

Beginner

Year 0 – 1The Power BI Fellowship · cheat sheet 1/3

01Power Query: a safe step order

Remove junk rows (Remove Top / Bottom Rows, not a value filter)
Promote headers → rename to PascalCase
Trim + Clean every text column
Replace values: $, USD, N/A → null
Change type (use Using Locale for dates and decimals)
Remove duplicates; remove errors only as a last resort
Disable load on staging queries (Reference, don't Duplicate)

Profile the entire data set, not the top 1,000 rows (click the status bar).

02Merge join kinds

Join	Keeps	Use it for
Left outer	All left rows + matches	Adding lookup columns (default)
Inner	Only matching rows	Strict filters. It silently drops rows!
Left anti	Left rows with no match	Finding orphan keys
Full outer	Everything	Reconciling two lists

Merge adds columns (JOIN). Append adds rows (UNION).

03Star schema rules

Fact = events (many rows, numbers, keys). Dimension = things you slice by.
Relationships 1:* from dimension → fact, single direction.
One continuous date table, marked as date table. Turn off Auto date/time.
Hide keys and raw numeric columns. Users drag measures.
Flatten snowflakes (Product → Category) into one dimension.
Know your grain: what does one row mean?

04Core measures

Total Qty = SUM ( FactSales[Quantity] )
Net Sales =
    SUMX ( FactSales,
        FactSales[Quantity] * FactSales[UnitPrice]
            * ( 1 - FactSales[Discount] ) )
Orders = DISTINCTCOUNT ( FactSales[OrderID] )
Avg Order Value = DIVIDE ( [Net Sales], [Orders] )
Margin % = DIVIDE ( [Net Sales] - [COGS], [Net Sales] )
% of Total =
    DIVIDE ( [Net Sales],
        CALCULATE ( [Net Sales],
            REMOVEFILTERS ( DimProduct[Category] ) ) )

Always DIVIDE, never /. Put measures in a _Measures table.

05Date table in one statement

Dates =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "MonthNumber", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "mmm" ),
    "Quarter", "Q" & QUARTER ( [Date] )
)

Then: Sort by column MonthName → MonthNumber, Mark as date table.

06Calculated column or measure?

	Column	Measure
Computed	At refresh, per row	At query time, per cell
Stored	Yes (uses memory)	No
Use on slicer/axis	Yes	No
Use for	Bands, flags, keys	Every number on a visual

07Pick the chart

Compare itemsBar / column (sorted)Trend over timeLinePart of wholeStacked / 100% bar (donut only 2–4 parts)Two numbersScatterOne headlineCard / KPIExact valuesTable / matrixWhereMap, only if location matters

08Report design rules

KPIs top-left, where eyes land first. Then trend, then detail.
6–8 visuals per page, max. Detail goes to drillthrough.
Titles state the insight ("West is 12% behind").
One accent colour for what matters, grey for the rest.
Alt text, contrast, never colour alone.
Build a mobile layout for pages people open on phones.

09Where to find it in Desktop

Sort by columnColumn toolsMark as date tableTable toolsManage relationshipsModeling / Model viewEdit interactionsFormat (visual selected)Sync slicersViewPerformance AnalyzerOptimize / ViewManage roles / View asModelingColumn profilingPower Query → View

10Service essentials

Workspace roles: Admin › Member › Contributor › Viewer. Consumers get Viewer or an App.
Share vs App: share = one item. App = packaged, with audiences, updates only when you click Update app.
Refresh: 8/day on Pro, 48/day on capacity. On-premises sources need a gateway.
Dashboards: pinned tiles + alerts. No slicers.
Publish to web = public internet. Never for internal data.

Intermediate

Year 1 – 3The Power BI Fellowship · cheat sheet 2/3

01CALCULATE modifiers

Modifier	Effect
`ALL(t / col)`	Removes every filter, including visual filters
`ALLSELECTED(…)`	Removes the row's filter, keeps the user's selection
`ALLEXCEPT(t, cols)`	Removes all filters on t except cols
`REMOVEFILTERS(…)`	Same as ALL inside CALCULATE, clearer name
`KEEPFILTERS(cond)`	Intersects instead of replacing
`USERELATIONSHIP(a,b)`	Uses an inactive relationship
`CROSSFILTER(a,b,BOTH)`	Changes direction in this measure only

A boolean filter (T[c] = "x") replaces existing filters on that column.

02Time intelligence

Sales YTD = TOTALYTD ( [Net Sales], Dates[Date] )
Sales PY  = CALCULATE ( [Net Sales], SAMEPERIODLASTYEAR ( Dates[Date] ) )
YoY %     = DIVIDE ( [Net Sales] - [Sales PY], [Sales PY] )
Sales PM  = CALCULATE ( [Net Sales], DATEADD ( Dates[Date], -1, MONTH ) )
Rolling 3M =
    CALCULATE ( [Net Sales],
        DATESINPERIOD ( Dates[Date], MAX ( Dates[Date] ), -3, MONTH ) )

Needs a continuous, marked date table covering whole years.

03Iterators and ranking

Customers Over 500 =
    COUNTROWS ( FILTER ( VALUES ( DimCustomer[CustomerKey] ),
        [Net Sales] > 500 ) )
Customer Rank =
    RANKX ( ALLSELECTED ( DimCustomer[CustomerName] ),
        [Net Sales], , DESC, DENSE )
Top Product =
    MAXX ( TOPN ( 1, VALUES ( DimProduct[ProductName] ), [Net Sales] ),
        DimProduct[ProductName] )

Iterate the smallest table that answers the question: a dimension key, not the fact.

04Variables and debugging

MoM % =
VAR cur  = [Net Sales]
VAR prev = [Sales PM]
RETURN DIVIDE ( cur - prev, prev )   -- debug: RETURN prev

Variables are evaluated once, where they're defined.
DAX Studio: Server Timings → storage engine vs formula engine ms.
Performance Analyzer → Copy query → run it in DAX Studio.

05Power Query M snippets

// safe conversion
try Number.From([Price], "en-US") otherwise null
// wide to tall
Table.UnpivotOtherColumns(Source, {"StoreCode"}, "Attribute", "Value")
// parameterised source
Csv.Document(Web.Contents(DataFolder & "FactSales.csv"))
// incremental refresh filter (must fold)
Table.SelectRows(Source, each [OrderDate] >= RangeStart
                             and [OrderDate] < RangeEnd)
// custom function
(t as nullable text) as nullable number =>
    try Number.From(Text.Remove(t, {"$", ","})) otherwise null

06Modeling patterns

Problem	Pattern
Facts at different grains	Shared dims at the common grain (DimCategory, DimMonth)
Neither side unique	Bridge table (or : with care)
Two dates on one fact	Inactive relationship + USERELATIONSHIP
Manager → employee	PATH, PATHITEM, PATHCONTAINS
Stock, balances	Semi-additive: last snapshot

07Row-level security

// static role, filter on DimRegion
[RegionName] = "West"
// dynamic, filter on DimRegion
CONTAINS ( UserRegionMapping,
    UserRegionMapping[UserEmail], USERPRINCIPALNAME (),
    UserRegionMapping[RegionKey], DimRegion[RegionKey] )
// org hierarchy, filter on DimEmployee
PATHCONTAINS ( DimEmployee[Path],
    LOOKUPVALUE ( DimEmployee[EmployeeKey],
        DimEmployee[Email], USERPRINCIPALNAME () ) )

Test with View as → Other user. RLS doesn't apply to Admin/Member/Contributor, and ALL() can't remove it.

08Interactive report features

DrillthroughField on target page · Keep all filters · auto back buttonTooltip pagePage info → Allow as tooltip · Canvas: TooltipBookmark toggleSelection pane + bookmarks with Data untickedField parameterModeling → New parameter → FieldsDynamic titleText measure → Title → fx → Field valueSync slicersView → Sync slicers

09Service operations

Gateway: standard mode for teams, UNC paths, add a second admin.
Refresh: failure notifications on. Read refresh history first.
Shared model: publish the model once, thin reports connect live, grant Build.
App audiences: hide pages per audience. Update the app to release changes.
Endorse: Promoted (anyone) vs Certified (authorised reviewers).

Advanced

Year 3 – 5The Power BI Fellowship · cheat sheet 3/3

01Performance workflow

Performance Analyzer: find the slow visual.
DAX Studio: Server Timings. High formula engine ms or CallbackDataID means rewrite the DAX.
VertiPaq Analyzer (View Metrics): the biggest columns and tables.
Fix the single biggest cause.
Measure again. Keep a before/after table.

Check the number is right before making it fast.

02Shrink the model

Remove unused columns: IDs, GUIDs, audit columns.
Reduce cardinality: split datetime into date + time, round decimals.
Use Fixed decimal / whole numbers where possible.
Auto date/time off.
No calculated columns on big facts. Push them upstream.
Pre-aggregate what nobody drills into.

03DAX that stays in the storage engine

Filter columns, not whole tables, in CALCULATE.
Iterate VALUES(Dim[Key]), not the fact table.
Store repeated sub-expressions in VAR.
Avoid bi-directional relationships. Use CROSSFILTER in one measure.
Beware IF and SWITCH on measures inside big iterators.

04Storage modes

Mode	Speed	Freshness	Watch out
Import	Fastest	Last refresh	Size limits, refresh windows
DirectQuery	Source-bound	Live	DAX limits, source load
Dual	Adaptive	Refresh	For dimensions in composites
Direct Lake	Near Import	Minutes	DirectQuery fallback, guardrails

05Incremental refresh checklist

RangeStart / RangeEnd Date/Time parameters (exact names).
>= start and < end, applied early, and it folds.
Policy: archive N years, refresh N days, detect changes (ModifiedDate).
First refresh loads everything. Schedule it off-hours.
Inspect partitions via the XMLA endpoint.

06Calculation groups

-- item "PM"
CALCULATE ( SELECTEDMEASURE (),
    DATEADD ( Dates[Date], -1, MONTH ) )
-- format string expression
SELECTEDMEASUREFORMATSTRING ()
-- limit to some measures
IF ( ISSELECTEDMEASURE ( [Net Sales], [COGS] ),
     <expression>, SELECTEDMEASURE () )

Set precedence when there are several groups. Implicit measures stop working.

07Advanced DAX patterns

Stock On Hand =
    CALCULATE ( SUM ( FactInventory[QuantityOnHand] ),
        LASTNONBLANK ( Dates[Date],
            CALCULATE ( SUM ( FactInventory[QuantityOnHand] ) ) ) )
Budget (virtual) =
    CALCULATE ( SUM ( FactBudget[BudgetAmount] ),
        TREATAS ( VALUES ( DimProduct[Category] ), FactBudget[Category] ) )
Running Sales =
    CALCULATE ( [Net Sales],
        WINDOW ( 1, ABS, 0, REL,
            ALLSELECTED ( DimCustomer[CustomerName] ),
            ORDERBY ( [Net Sales], DESC ) ) )

08Deployment and ALM

PBIP + TMDL: text files in Git, reviewable diffs.
Git integration: workspace ↔ branch, commit and update.
Deployment pipelines: Dev → Test → Prod, with parameter and data source rules.
XMLA endpoint: Tabular Editor/SSMS on published models (after that, no PBIX download).
Best Practice Analyzer in CI. Tag releases (v1.0) for rollback.

09Fabric in one box

OneLakeOne lake per tenant, Delta tables, shortcutsLakehouseFiles + tables, Spark, read-only SQL endpointWarehouseFull T-SQL read/writeDataflow Gen2Power Query → Lakehouse/WarehouseDirect LakeModel reads Delta directly. Views/RLS on the SQL endpoint trigger fallback.MedallionBronze raw → Silver clean → Gold starCapacityF SKUs, CU smoothing, Capacity Metrics app

10Governance checklist

Tenant settings: Publish to web off, export restricted to groups.
Certified shared models, with owners and descriptions.
Sensitivity labels on models and reports. OLS for salary-type columns.
Usage metrics + lineage before changing or retiring anything.
Naming conventions for workspaces, models and measures.

11The senior conversation

Problem → Impact → Options → Recommendation → Ask.

Executives: time, money, risk. No engine names.
Engineers: evidence (timings, sizes), then the change you need from them.
Always end with a recommendation and a decision to make.