32. Keyboard Shortcuts and DAX Cheat Sheet
32.2 DAX Quick Reference
| Category | Function | Syntax | Purpose |
|---|---|---|---|
| Aggregation | SUM | SUM(col) |
Adds all numbers in a column |
| Aggregation | AVERAGE | AVERAGE(col) |
Arithmetic mean |
| Aggregation | MIN / MAX | MIN(col) / MAX(col) |
Smallest / largest value |
| Aggregation | COUNT / COUNTA | COUNT(col) |
Non-blank values |
| Aggregation | COUNTROWS | COUNTROWS(table) |
Number of rows |
| Aggregation | DISTINCTCOUNT | DISTINCTCOUNT(col) |
Number of unique values |
| Iterator | SUMX / AVERAGEX | SUMX(table, expr) |
Row-by-row expression, then aggregate |
| Iterator | MINX / MAXX / COUNTX | MAXX(table, expr) |
Row-by-row min/max/count |
| Logical | IF | IF(cond, then, [else]) |
Conditional result |
| Logical | SWITCH | SWITCH(expr, v1, r1, …, [else]) |
Multiple conditions; SWITCH(TRUE(), …) pattern |
| Logical | AND / OR / NOT | AND(a, b) · a && b · a || b |
Combine conditions |
| Logical | COALESCE | COALESCE(v1, v2, …) |
First non-blank value |
| Math | DIVIDE | DIVIDE(num, den, [alt]) |
Safe division |
| Math | ROUND | ROUND(num, digits) |
Round a value |
| Filter | CALCULATE | CALCULATE(expr, filter1, …) |
Evaluate in modified filter context |
| Filter | CALCULATETABLE | CALCULATETABLE(table, filter1, …) |
Table version of CALCULATE |
| Filter | FILTER | FILTER(table, condition) |
Rows meeting a condition |
| Filter | ALL | ALL(table or col, …) |
Remove filters / all rows |
| Filter | ALLEXCEPT | ALLEXCEPT(table, col, …) |
Remove filters except listed columns |
| Filter | ALLSELECTED | ALLSELECTED([table or col]) |
Keep outer (slicer) filters only |
| Filter | REMOVEFILTERS | REMOVEFILTERS([table or col]) |
Remove filters (CALCULATE modifier) |
| Filter | KEEPFILTERS | KEEPFILTERS(filter) |
Intersect with existing filters |
| Relationship | RELATED | RELATED(col) |
Value from the one side |
| Relationship | RELATEDTABLE | RELATEDTABLE(table) |
Rows from the many side |
| Relationship | USERELATIONSHIP | USERELATIONSHIP(col1, col2) |
Activate inactive relationship |
| Relationship | CROSSFILTER | CROSSFILTER(col1, col2, dir) |
Change filter direction for a calculation |
| Information | VALUES / DISTINCT | VALUES(col) |
Distinct visible values |
| Information | SELECTEDVALUE | SELECTEDVALUE(col, [alt]) |
Single selected value or alternate |
| Information | HASONEVALUE | HASONEVALUE(col) |
TRUE if one value visible |
| Information | ISBLANK / ISINSCOPE | ISBLANK(value) |
Tests |
| Text | CONCATENATE | CONCATENATE(text1, text2) |
Join two strings (use & for more) |
| Text | CONCATENATEX | CONCATENATEX(table, expr, [delim]) |
Join values from a table |
| Text | LEFT / RIGHT / MID | LEFT(text, n) · MID(text, start, n) |
Extract characters |
| Text | LEN / UPPER / LOWER / TRIM | LEN(text) |
Length and cleaning |
| Text | FORMAT | FORMAT(value, "format") |
Number/date to formatted text |
| Text | SUBSTITUTE / SEARCH | SUBSTITUTE(text, old, new) |
Replace / find text |
| Date | TODAY / NOW | TODAY() |
Current date / date-time |
| Date | YEAR / MONTH / DAY | YEAR(date) |
Parts of a date |
| Date | DATE | DATE(y, m, d) |
Build a date |
| Date | DATEDIFF | DATEDIFF(start, end, DAY) |
Difference in intervals |
| Date | EOMONTH / EDATE | EOMONTH(date, months) |
End of month / add months |
| Date | CALENDAR | CALENDAR(start, end) |
Date table between dates |
| Date | CALENDARAUTO | CALENDARAUTO([fy_end_month]) |
Automatic date table |
| Time intel. | TOTALYTD / QTD / MTD | TOTALYTD(expr, dates, [filter], [ye]) |
Period-to-date totals |
| Time intel. | DATESYTD | DATESYTD(dates, [ye]) |
Dates from year start |
| Time intel. | SAMEPERIODLASTYEAR | SAMEPERIODLASTYEAR(dates) |
Same dates one year back |
| Time intel. | DATEADD | DATEADD(dates, n, interval) |
Shift dates |
| Time intel. | PARALLELPERIOD | PARALLELPERIOD(dates, n, interval) |
Full shifted period |
| Time intel. | DATESINPERIOD | DATESINPERIOD(dates, start, n, interval) |
Rolling window |
| Time intel. | DATESBETWEEN | DATESBETWEEN(dates, start, end) |
Dates in a range |
| Ranking | RANKX | RANKX(table, expr, [val], [order], [ties]) |
Rank items |
| Ranking | TOPN | TOPN(n, table, orderBy, [order]) |
Top N rows |
| Table | SUMMARIZE / ADDCOLUMNS | ADDCOLUMNS(table, "Name", expr) |
Build table expressions |
| Table | GENERATESERIES | GENERATESERIES(start, end, step) |
Number series (what-if) |
| Security | USERPRINCIPALNAME | USERPRINCIPALNAME() |
Current user's UPN (for RLS) |
| Variables | VAR / RETURN | VAR x = … RETURN … |
Store intermediate results |
Ravindra Bagale's Tip
Ek common chuk mhanje copying functions from the cheat sheet without checking the argument order. Hover over the function in the formula bar to see its syntax, and test it in a small table visual first. Dhyan rakho!