Skip to content
Tutorials Center
My learning

Topics

Lesson 56 of 56 ยท Practice

Power BI Cheat Sheet

A one-page Power BI reference: DAX functions and patterns, Power Query M steps, modelling rules, shortcuts and a performance checklist, with runnable pandas stand-ins.

This is the page to keep open while you build reports. It does not teach from scratch; it collects the things people look up again and again: the DAX functions you actually use, the Power Query (M) steps behind the buttons, rules for a healthy model, a few keyboard tips and a checklist for slow reports. Each block links to the lesson that explains it.

Power BI is a desktop and cloud tool, so the DAX and M here are read-only reference. Three runnable pandas stand-ins at the end give you the same operations in a form you can edit: a measure-to-pandas translation, an M-style pipeline and a small model checker that tests the rules in the modelling section.

Where each thing lives

Power Query Shape and clean rows before they load.
Model Tables, keys and relationships.
DAX Measures that react to filters.
Report Visuals, slicers and formatting.

Fix a problem as far upstream as you can.

1 / 2

DAX quick reference

Aggregation and counting

Function Use
SUM, AVERAGE, MIN, MAX Aggregate one column
COUNT, COUNTA Count values in a column
COUNTROWS ( Table ) Number of rows in a table
DISTINCTCOUNT ( col ) Number of different values
SUMX, AVERAGEX, MAXX, COUNTX Iterate rows, evaluate an expression, aggregate
DIVIDE ( a, b, [alt] ) Safe division, blank on zero

Logic and information

Function Use
IF, SWITCH ( TRUE (), cond1, r1, cond2, r2, else ) Branching
AND, OR, &&, ||, NOT Boolean logic
ISBLANK, COALESCE ( a, b ) Handle blanks
SELECTEDVALUE ( col, [alt] ) The single selected value, or a default
HASONEVALUE, ISFILTERED, ISINSCOPE Detect what the visual is showing
USERPRINCIPALNAME () The current user's sign-in name (for RLS)

Filters and context

Function Use
CALCULATE ( expr, filter1, filter2 ) Evaluate in a modified filter context
FILTER ( table, condition ) Row-by-row filter (an iterator)
ALL, REMOVEFILTERS Remove filters from a table or columns
ALLEXCEPT ( table, col1, col2 ) Remove all filters except the listed columns
ALLSELECTED Remove visual filters, keep outer ones such as slicers
KEEPFILTERS Intersect with, instead of replacing, an existing filter
VALUES, DISTINCT The distinct values in the current context
RELATED ( col ), RELATEDTABLE ( table ) Follow a relationship from a row
USERELATIONSHIP, CROSSFILTER Switch on an inactive relationship, change direction

Filter arguments in CALCULATE replace existing filters on the same column. See CALCULATE and Filter Context.

Time intelligence

Needs a marked date table with a continuous Date column (see Date Tables).

Function Use
TOTALYTD, TOTALQTD, TOTALMTD Period to date
SAMEPERIODLASTYEAR ( dates ) The same dates one year earlier
DATEADD ( dates, -1, MONTH ) Shift by a number of intervals
DATESINPERIOD ( dates, end, -3, MONTH ) A window ending at a date
DATESBETWEEN ( dates, start, end ) An explicit range
PARALLELPERIOD, EOMONTH Whole-period shift, month end

Ranking, tables and text

Function Use
RANKX ( table, expr, , DESC ) Rank by a measure
TOPN ( n, table, expr ) The top n rows
ADDCOLUMNS, SUMMARIZE, SUMMARIZECOLUMNS Build or group tables
DATATABLE, UNION, GENERATESERIES Create small tables
FORMAT, CONCATENATEX, LEFT, SEARCH Text helpers
EVALUATE, DEFINE DAX Query view: run and test a query

Common DAX patterns

DAX
Percent of Total =
DIVIDE ( [Sales], CALCULATE ( [Sales], REMOVEFILTERS ( Product ) ) )

Sales Last Year =
CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

YoY % = DIVIDE ( [Sales] - [Sales Last Year], [Sales Last Year] )

Rolling 3 Months =
CALCULATE ( [Sales], DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH ) )

Running Total =
CALCULATE (
    [Sales],
    FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

Customer Rank = RANKX ( ALLSELECTED ( Customer[Name] ), [Sales], , DESC )

Use variables (VAR ... RETURN) to name intermediate results; they are evaluated once and make the formula readable. More patterns are in Common DAX Patterns, and debugging advice in Debugging and Optimizing DAX.

Power Query (M) quick reference

Every step you click in the editor is an M expression in a let ... in block (see The Power Query Editor).

Task M function
Filter rows Table.SelectRows ( t, each [Amount] > 0 )
Keep or remove columns Table.SelectColumns, Table.RemoveColumns
Rename Table.RenameColumns ( t, {{"old", "new"}} )
Change type Table.TransformColumnTypes ( t, {{"Date", type date}} )
Add column Table.AddColumn ( t, "Total", each [Qty] * [Price], type number )
Replace values Table.ReplaceValue ( t, old, new, Replacer.ReplaceValue, {"col"} )
Remove duplicates Table.Distinct ( t, {"ID"} )
Sort Table.Sort ( t, {{"Amount", Order.Descending}} )
Group Table.Group ( t, {"Region"}, {{"Total", each List.Sum([Amount]), type number}} )
Append Table.Combine ( {t1, t2} )
Merge Table.NestedJoin, then Table.ExpandTableColumn
Unpivot Table.UnpivotOtherColumns ( t, {"Product"}, "Month", "Units" )
Pivot Table.Pivot
Errors try expr otherwise fallback

Text and date helpers: Text.Trim, Text.Upper, Text.Proper, Text.Split, Text.Combine, Date.Year, Date.StartOfMonth, Date.AddMonths. In each, the underscore _ is the current row or item, and [Column] is shorthand for _[Column]. See M Language Basics and Custom Functions and Reusable Queries.

Modelling rules

  1. Star schema. Facts in the middle, dimensions around them. Avoid one giant flat table.
  2. State the grain of every fact table, and never mix grains in one table.
  3. Unique keys on the "one" side. Relationships are one-to-many from dimension to fact, with a single filter direction.
  4. One date table, marked as such, continuous, with role-playing dates handled by inactive relationships.
  5. Hide key columns, raw numeric columns and helper tables from report authors; expose measures.
  6. Measures, not implicit sums. Name them clearly, put them in a measure table or display folder and set formats.
  7. Reduce columns and cardinality. Remove unused columns; split date-times into a date and a time; avoid long unique text columns.
  8. Decide storage mode on purpose: Import by default, DirectQuery when freshness or volume forces it, Dual for dimensions in composite models.

See Star Schema, Relationships and Model View, Hierarchies, Formatting and Display Folders.

Keyboard and editing tips

Shortcuts differ a little between releases, so treat this as a starting list and check the keyboard shortcut help in your version.

  • Ctrl+S saves, Ctrl+Z and Ctrl+Y undo and redo, Ctrl+C and Ctrl+V copy and paste visuals (including between pages).
  • In the formula bar, Enter commits a measure, and Shift+Enter or Alt+Enter starts a new line so formulas stay readable.
  • Start typing to get IntelliSense, and press Tab to accept a suggestion.
  • In DAX query view, F5 or Ctrl+Enter runs the query.
  • In the report canvas, hold Ctrl and click to select several visuals, then use the Format menu to align and distribute them.
  • In a slicer, hold Ctrl and click to select several items.

Performance checklist

  • Star schema in place; no unnecessary bidirectional filters or many-to-many relationships.
  • Unused columns and tables removed; large text and GUID columns left out where possible.
  • Filters and column selection happen early in Power Query, and folding is intact (View Native Query).
  • Heavy transformations done in the source or a dataflow, not repeated in every report.
  • Measures use variables and avoid iterating huge tables when an aggregator would do.
  • Fewer visuals per page; tables with thousands of rows replaced by aggregated visuals or drill-through.
  • Incremental refresh for large, append-mostly fact tables.
  • Performance Analyzer run on the slowest page; its worst query tested in DAX query view or DAX Studio.

See Query Folding, Incremental Refresh and Query Performance and Governance, Deployment Pipelines and Best Practices.

Runnable stand-ins

The three examples below run in your browser with pandas. They are not Power BI; they give you the same results as the measures, M steps and model rules above, so you can edit and test.

First, the most common measures in pandas form: aggregations, a filtered measure like CALCULATE, a percent of total and a safe division.

Python Stand-in: measures in pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Next, an M-style pipeline. Each line is one applied step, in the same order you would see in the Power Query editor.

Python Stand-in: a Power Query pipeline

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Finally, a small model checker. It tests two rules from the modelling section: dimension keys must be unique, and every fact key must exist in its dimension.

Python Stand-in: a model rule checker

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In Power BI, the same problems appear as a failed one-to-many relationship (duplicates) and as a (Blank) row in visuals (orphans). Fix them at the source or in Power Query, not with a many-to-many relationship.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which function should you use to divide when the denominator may be zero?

    DIVIDE returns blank (or an alternative result you provide) when the denominator is zero or blank, whereas / produces an error or infinity.
  2. 2. What does the M step Table.UnpivotOtherColumns ( t, {"Product"}, "Month", "Units" ) do?

    All columns other than the ones listed are turned into attribute-value pairs, here stored in the Month and Units columns.
  3. 3. Which is a rule for a healthy relationship?

    A one-to-many relationship needs a unique key on the one side. Duplicates break the relationship or inflate totals.
  4. 4. A report is slow. What should you do first?

    Measure before changing anything. Performance Analyzer shows which visual is slow and gives you the query to test in DAX query view or DAX Studio.

Summary

  • Keep the DAX tables handy: aggregators, iterators, CALCULATE and its filter modifiers, time intelligence and ranking.
  • Most Power Query work is a short list of Table.* functions in a let ... in block; each and _ mean the current row.
  • A healthy model is a star schema with stated grain, unique dimension keys, one marked date table and hidden helper columns.
  • Shortcuts change between versions: confirm them in the help, and use IntelliSense and Shift+Enter in the formula bar.
  • When a report is slow, measure first, then fix the model, the folding and the heaviest measures.

You now have the full Power BI toolkit: go back to any lesson from the Power BI tutorial when you need the detail.