WMA Hub

Cheat sheets

One page each. Print them, pin them, share them.

DAX context transition

Row context knows which row you are on. Filter context knows which rows are visible. CALCULATE turns the first into the second.

The two contexts

  • Filter context: The set of filters applied by slicers, visuals, rows/columns of a matrix and CALCULATE. Measures are evaluated inside it.
  • Row context: The "current row" inside a calculated column or an iterator (SUMX, FILTER, ADDCOLUMNS...). It does not filter other tables by itself.

Context transition

  • What it is: When CALCULATE (or a measure reference) is evaluated inside a row context, every column of the current row becomes a filter.
  • Implicit CALCULATE: Referencing a measure inside an iterator wraps it in CALCULATE automatically, so [Total Sales] in SUMX behaves per row.
  • Calculated column: SUM(Sales[Amount]) in a column sums the whole table; CALCULATE(SUM(Sales[Amount])) sums only rows equal to the current row (all columns).

Watch out

  • Duplicate rows: If two rows are identical, transition filters both, so each row sees both. Add a unique key column.
  • Cost: Context transition inside a large iterator is slow. Prefer aggregating a measure over a smaller table of distinct values.
  • Sort of check: Test the measure in a table visual with the row key on rows, and compare with a manual total.

DAX time intelligence

Time intelligence needs a proper date table. Then a handful of functions cover nearly every report.

Date table rules

  • One row per day: Contiguous dates with no gaps, covering full calendar years.
  • Mark it: Mark it as the date table and relate it to the date column of each fact table.
  • One per role: Use one active relationship; use USERELATIONSHIP for others (order date, ship date).

Common patterns

  • Year to date: TOTALYTD([Sales], 'Date'[Date])
  • Same period last year: CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))
  • Shift by an interval: CALCULATE([Sales], DATEADD('Date'[Date], -1, MONTH))
  • Rolling 3 months: CALCULATE([Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH))
  • Year-over-year %: DIVIDE([Sales] - [Sales LY], [Sales LY])
  • Month to date: TOTALMTD([Sales], 'Date'[Date])

Gotchas

  • Fiscal years: Pass the year-end date as the optional argument, for example TOTALYTD([Sales], 'Date'[Date], "30/6").
  • Partial periods: Compare like with like: limit last year to the dates that exist this year if the current period is incomplete.

CALCULATE filter modifiers

CALCULATE changes the filter context. These functions say how.

Remove or keep filters

  • ALL(Table or column): Removes every filter on the table or the listed columns. Use for "total of everything".
  • REMOVEFILTERS(...): The readable alias of ALL inside CALCULATE; same effect.
  • ALLEXCEPT(Table, col...): Removes all filters on a table except those on the listed columns.
  • ALLSELECTED(...): Removes filters from the visual but keeps the outer slicers and page filters. Use for "share of selection".
  • KEEPFILTERS(...): Adds a filter without replacing the existing filter on that column.
  • VALUES(col) / SELECTEDVALUE(col): Reads the currently visible values of a column.

Change relationships

  • USERELATIONSHIP(a, b): Activates an inactive relationship for this calculation.
  • CROSSFILTER(a, b, direction): Changes the cross-filter direction for this calculation.
  • TREATAS(table, col...): Applies a set of values as filters to columns that have no relationship.

Evaluation order

  • 1: Existing filter context from the visual.
  • 2: Filter arguments of CALCULATE are applied; same-column filters replace the old ones.
  • 3: Context transition (if called in a row context) happens first, before the other filters.

Power BI relationship types

A good model is mostly boring one-to-many relationships.

Cardinality

  • One-to-many (1:*): The default and the one you want. A dimension row filters many fact rows.
  • One-to-one (1:1): Both sides unique. Often a sign the tables should be merged.
  • Many-to-many (*:*): Neither side unique. Powerful but risky; prefer a bridge table with two 1:* relationships.

Direction and state

  • Single direction: Filters flow from the "one" side to the "many" side. Recommended.
  • Both directions: Use sparingly (for example for a bridge table or row-level security). It can create ambiguity and slow queries.
  • Active / inactive: Only one active path between two tables. Others are inactive and used with USERELATIONSHIP.

Model shapes

  • Star schema: One fact table surrounded by dimension tables. Fast, simple, easy to explain.
  • Snowflake: Dimensions split into sub-dimensions. Flatten in Power Query when you can.
  • Role-playing dimension: One date table used for order date and ship date: one active, one inactive relationship.
  • Composite model: Mixes Import and DirectQuery tables. Check storage mode of each table and aggregations.