Downloads
Reference

SQL and DAX cheat sheets

The syntax you reach for most, on one page each. Get a branded PDF to print or keep.

SQLDAX

SQL cheat sheet

Read rows

SELECT col1, col2
FROM schema.table
WHERE col3 > 10 AND col4 IS NOT NULL
ORDER BY col1 DESC;

SQL Server: SELECT TOP 10 .... MariaDB: ... LIMIT 10.

Filter

WHERE x = 5   WHERE x <> 5
WHERE x BETWEEN 1 AND 9
WHERE x IN (1, 2, 3)
WHERE name LIKE 'Ab%'
WHERE x IS NULL

Never = NULL. Combine with AND, OR, NOT (use parentheses).

Join

FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
LEFT JOIN payments p ON p.order_id = o.id

INNER keeps matches only. LEFT keeps every row on the left. Always give the ON condition.

Group and aggregate

SELECT category, COUNT(*) AS n, SUM(amount) AS total
FROM sales
GROUP BY category
HAVING SUM(amount) > 1000
ORDER BY total DESC;

WHERE filters rows before grouping; HAVING filters groups after.

Combine and nest

WITH big AS (
  SELECT customer_id, SUM(amount) AS total
  FROM sales GROUP BY customer_id)
SELECT * FROM big WHERE total > 5000;

SELECT a FROM t1 UNION SELECT a FROM t2;

UNION removes duplicates; UNION ALL keeps them.

Window functions

SELECT order_id, amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn,
  SUM(amount) OVER (ORDER BY order_date) AS running,
  AVG(amount) OVER (PARTITION BY customer_id) AS cust_avg
FROM sales;

They calculate across related rows without collapsing them.

Text, numbers, dates

UPPER(x) LOWER(x) TRIM(x) LEN(x)       -- LENGTH() in MariaDB
CONCAT(a, ' ', b)   SUBSTRING(x, 1, 3)
ROUND(x, 2)  COALESCE(x, 0)  CAST(x AS int)
YEAR(d) MONTH(d)  DATEADD(day, 7, d)   -- DATE_ADD() in MariaDB

Conditional logic

CASE WHEN score >= 90 THEN 'A'
     WHEN score >= 80 THEN 'B'
     ELSE 'C' END AS grade

SQL Server versus MariaDB

SQL ServerMariaDB
Row limitTOP 10LIMIT 10
Quote a name[Order Details]`Order Details`
Other databasedb.schema.tabledb.table
TodayGETDATE()NOW()
Text join+ or CONCATCONCAT

DAX cheat sheet

Measure basics

Total Sales = SUM(Sales[SalesAmount])
Margin % = DIVIDE([Gross Margin], [Total Sales])
Orders = DISTINCTCOUNT(Sales[OrderID])
Revenue = SUMX(Sales, Sales[Qty] * Sales[Price])

Columns are Table[Column], measures are [Measure]. Use DIVIDE, not /.

Variables

Margin % =
VAR S = [Total Sales]
VAR C = [Total Cost]
RETURN DIVIDE(S - C, S)

Evaluated once, readable, and fast.

CALCULATE

Online Sales =
CALCULATE([Total Sales], Channel[Channel] = "Online")

All Channels =
CALCULATE([Total Sales], REMOVEFILTERS(Channel))

Boolean filters replace the column's filter. KEEPFILTERS intersects instead.

Percent of total

% of Total = DIVIDE([Total Sales],
  CALCULATE([Total Sales], REMOVEFILTERS()))
% of Selected = DIVIDE([Total Sales],
  CALCULATE([Total Sales], ALLSELECTED(Category)))

ALL ignores the slicer; ALLSELECTED respects it.

Time intelligence

Sales YTD = TOTALYTD([Total Sales], 'Calendar'[Date])
Sales PY = CALCULATE([Total Sales],
  SAMEPERIODLASTYEAR('Calendar'[Date]))
YoY % = DIVIDE([Total Sales] - [Sales PY], [Sales PY])
Rolling 12M = CALCULATE([Total Sales],
  DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH))

Needs a Calendar table marked as the date table.

Ranking and Top N

Rank = RANKX(ALLSELECTED(Product[Product]),
  [Total Sales], , DESC, Dense)
Top 10 = CALCULATE([Total Sales],
  TOPN(10, ALLSELECTED(Product[Product]), [Total Sales]))

Relationships

Brand = RELATED(Product[Brand])      -- calculated column
Ship Date Sales = CALCULATE([Total Sales],
  USERELATIONSHIP(Sales[ShipDate], 'Calendar'[Date]))

What is in context?

SELECTEDVALUE(Col, "All")   HASONEVALUE(Col)
ISFILTERED(Col)   ISINSCOPE(Col)
VALUES(Col)   COUNTROWS(table)

Mistakes to avoid

  • Aggregating an expression: use SUMX, not SUM(a*b).
  • A measure inside a Boolean CALCULATE filter: wrap it in FILTER.
  • IFERROR hides real errors; test with DIVIDE or ISBLANK.
  • Time intelligence on a text or fact-table date: use a marked Calendar.
  • Formatting with FORMAT in a measure turns numbers into text.

More depth in the Analyze guides, the function reference and the glossary.