learn.fttgsolutions.com · Cheat Sheets
Measure. Calculate. Filter.
| SUM | SUM(table[column]) | Adds all numeric values in a column. The baseline aggregation. |
| SUMX | SUMX(table, expression) | Iterates every row of a table, evaluates an expression per row, then sums the results. |
| AVERAGE | AVERAGE(table[column]) | Returns the arithmetic mean of all non-blank numeric values in a column. |
| AVERAGEX | AVERAGEX(table, expression) | Iterates a table, evaluates an expression per row, then returns the mean. |
| GEOMEAN | GEOMEAN(table[column]) | Returns the geometric mean of all values in a column. Useful for growth rates and ratios. |
| GEOMEANX | GEOMEANX(table, expression) | Iterates a table, evaluates an expression per row, and returns the geometric mean of the results. |
| MIN / MAX | MIN(table[column])
MAX(table[column]) | Returns the smallest or largest value in a column. Also works on two scalar arguments. |
| MINX / MAXX | MINX(table, expression)
MAXX(table, expression) | Iterates a table, evaluates an expression per row, and returns the min or max result. |
| COUNT | COUNT(table[column]) | Counts numeric non-blank values in a column. Use COUNTA for text columns. |
| COUNTA | COUNTA(table[column]) | Counts non-blank values of any data type in a column. |
| COUNTROWS | COUNTROWS(table) | Returns the number of rows in a table or table expression. Often used with FILTER. |
| COUNTBLANK | COUNTBLANK(table[column]) | Counts the number of blank (empty or null) cells in a column. |
| DISTINCTCOUNT | DISTINCTCOUNT(table[column]) | Counts the number of unique non-blank values in a column. |
| COUNTX | COUNTX(table, expression) | Iterates a table and counts the rows where the expression is non-blank. |
| PRODUCT | PRODUCT(table[column]) | Multiplies all values in a column together and returns the result. |
| PRODUCTX | PRODUCTX(table, expression) | Iterates a table, evaluates an expression per row, then multiplies all results. |
| CALCULATE | CALCULATE(expression, filter1, filter2, ...) | Evaluates an expression after modifying the filter context. The most important DAX function — it is what makes context transitions possible. |
| CALCULATETABLE | CALCULATETABLE(table, filter1, ...) | Same as CALCULATE but returns a filtered table instead of a scalar value. |
| FILTER | FILTER(table, condition) | Returns a table containing only the rows where the condition is TRUE. Often used inside CALCULATE to pass row-level conditions. |
| ALL | ALL(table)
ALL(table[column]) | Removes all filters from the specified table or column. Use inside CALCULATE to ignore slicers. |
| ALLEXCEPT | ALLEXCEPT(table, table[col1], ...) | Removes all filters from a table except the specified columns. Useful for keeping one dimension active while clearing others. |
| ALLSELECTED | ALLSELECTED(table[column]) | Removes filters applied inside the current visual but keeps filters from slicers and other visuals — used for percent-of-visual-total patterns. |
| ALLNOBLANKROW | ALLNOBLANKROW(table[column]) | Same as ALL but also removes the blank row that DAX adds to handle unmatched relationship values. Use when blank rows distort totals. |
| REMOVEFILTERS | REMOVEFILTERS(table[column]) | Clearer alias for ALL when used as a CALCULATE modifier. Removes filters from the specified table or column. |
| KEEPFILTERS | KEEPFILTERS(filter_expression) | Adds a filter to the context without overriding existing filters on the same column — used inside CALCULATE. |
| ALLCROSSFILTERED | ALLCROSSFILTERED(table) | Removes all filters including those applied via cross-filter relationships. |
| CROSSFILTER | CROSSFILTER(col1, col2, direction) | Controls the cross-filter direction for a relationship within a CALCULATE expression. Direction: BOTH, ONEWAY, or NONE. |
| USERELATIONSHIP | USERELATIONSHIP(table1[col], table2[col]) | Activates an inactive relationship within a CALCULATE expression. Essential for role-playing dimension patterns (e.g. multiple date relationships). |
| VAR / RETURN | VAR myValue = CALCULATE(SUM(Sales[Amount]))
RETURN
myValue * 0.1 | Stores intermediate results to avoid repeated calculation and improve readability. Variables fix the filter context at the point of declaration. |
| Multiple VAR | VAR base = SUM(Sales[Amount])
VAR target = [TargetMeasure]
VAR pct = DIVIDE(base, target)
RETURN pct | Chain multiple VAR declarations before a single RETURN. Each VAR can reference variables declared above it. |
| COLUMN | COLUMN(table[column]) = expression | Declares a calculated column in a table. Evaluated row by row in the row context of the table — unlike measures which are evaluated in filter context. |
| ORDER BY | EVALUATE
SUMMARIZE(Sales, Sales[Region], "Total", SUM(Sales[Amount]))
ORDER BY [Total] DESC | Sorts the result of an EVALUATE statement. Used in DAX Studio and direct query contexts, not in Power BI measures. |
| IF | IF(condition, value_if_true, value_if_false) | Evaluates a condition and returns one of two values. The false argument is optional and defaults to BLANK(). |
| SWITCH | SWITCH(expression,
value1, result1,
value2, result2,
else_result) | Evaluates an expression against a list of values and returns the matching result. Use SWITCH(TRUE(), ...) for range-based conditions. |
| SWITCH(TRUE()) | SWITCH(TRUE(),
[Sales] > 100000, "High",
[Sales] > 50000, "Mid",
"Low") | SWITCH with TRUE() as the expression — evaluates each condition in order and returns the first TRUE match. The cleanest way to replace nested IFs. |
| IFERROR | IFERROR(expression, value_if_error) | Returns the expression result, or a fallback value if the expression raises any error. Avoid overusing — it can hide real bugs. |
| COALESCE | COALESCE(expr1, expr2, ...) | Returns the first non-blank, non-null value from the list of arguments. Cleaner than nested IFs for null-handling chains. |
| BLANK | BLANK() | Returns a blank (null) value. DAX treats BLANK as 0 in numeric operations and as empty string in text operations. |
| AND / OR | AND(condition1, condition2)
OR(condition1, condition2) | Logical AND/OR for exactly two arguments. For multiple conditions, use && and || operators instead. |
| NOT | NOT(condition) | Returns the logical opposite of a boolean expression. |
| TOTALYTD | TOTALYTD(expression, dates[Date])
TOTALYTD(expression, dates[Date], "6/30") | Calculates year-to-date for the expression. The third argument sets a non-calendar fiscal year end. |
| TOTALQTD | TOTALQTD(expression, dates[Date]) | Calculates quarter-to-date for the expression based on the current filter context. |
| TOTALMTD | TOTALMTD(expression, dates[Date]) | Calculates month-to-date for the expression based on the current filter context. |
| DATESYTD | DATESYTD(dates[Date])
DATESYTD(dates[Date], "6/30") | Returns a table of dates from the start of the year to the last date in context. Use inside CALCULATE for custom YTD measures. |
| DATESQTD | DATESQTD(dates[Date]) | Returns a table of dates from the start of the current quarter to the last date in context. |
| DATESMTD | DATESMTD(dates[Date]) | Returns a table of dates from the start of the current month to the last date in context. |
| SAMEPERIODLASTYEAR | SAMEPERIODLASTYEAR(dates[Date]) | Returns dates shifted back exactly one year. Use inside CALCULATE for year-over-year comparisons. |
| DATEADD | DATEADD(dates[Date], -1, YEAR)
DATEADD(dates[Date], -3, MONTH) | Shifts a set of dates by a specified number of intervals (DAY, MONTH, QUARTER, YEAR). Can go forward or backward. |
| PARALLELPERIOD | PARALLELPERIOD(dates[Date], -1, YEAR) | Returns the full parallel period (not a partial shift). Unlike DATEADD, always returns a complete period. |
| PREVIOUSMONTH | PREVIOUSMONTH(dates[Date]) | Returns all dates in the month immediately before the current filter context month. |
| PREVIOUSQUARTER | PREVIOUSQUARTER(dates[Date]) | Returns all dates in the quarter immediately before the current filter context quarter. |
| PREVIOUSYEAR | PREVIOUSYEAR(dates[Date]) | Returns all dates in the year immediately before the current filter context year. |
| NEXTMONTH | NEXTMONTH(dates[Date]) | Returns all dates in the month immediately after the current filter context month. |
| NEXTYEAR | NEXTYEAR(dates[Date]) | Returns all dates in the year immediately after the current filter context year. |
| DATESBETWEEN | DATESBETWEEN(dates[Date], startDate, endDate) | Returns a table of dates within a custom date range. Both boundaries are inclusive. |
| STARTOFMONTH | STARTOFMONTH(dates[Date]) | Returns the first date of the month in the current filter context. |
| ENDOFMONTH | ENDOFMONTH(dates[Date]) | Returns the last date of the month in the current filter context. |
| STARTOFQUARTER | STARTOFQUARTER(dates[Date]) | Returns the first date of the quarter in the current filter context. |
| ENDOFQUARTER | ENDOFQUARTER(dates[Date]) | Returns the last date of the quarter in the current filter context. |
| STARTOFYEAR | STARTOFYEAR(dates[Date]) | Returns the first date of the year in the current filter context. |
| ENDOFYEAR | ENDOFYEAR(dates[Date]) | Returns the last date of the year in the current filter context. |
| TODAY | TODAY() | Returns today's date as a date value. Refreshes on each model recalculation. |
| NOW | NOW() | Returns the current date and time as a datetime value. |
| DATE | DATE(year, month, day) | Constructs a date value from year, month, and day integers. |
| DATEVALUE | DATEVALUE("2025-01-15") | Converts a date stored as text into a datetime value. Useful when importing data where dates arrive as strings. |
| YEAR / MONTH / DAY | YEAR(table[Date])
MONTH(table[Date])
DAY(table[Date]) | Extracts the year, month number (1–12), or day of month from a date column. |
| QUARTER | QUARTER(table[Date]) | Returns the quarter number (1–4) for a given date. Used to build fiscal quarter labels in calculated columns. |
| WEEKDAY | WEEKDAY(table[Date], 2) | Returns the day of the week as a number. Second argument sets the start: 1=Sunday, 2=Monday. |
| WEEKNUM | WEEKNUM(table[Date], 2) | Returns the ISO week number for a date. Second argument controls the week start. |
| DATEDIFF | DATEDIFF(startDate, endDate, interval) | Returns the number of intervals between two dates. Interval: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR. |
| EOMONTH | EOMONTH(startDate, months) | Returns the last day of the month, a given number of months before or after a start date. |
| EDATE | EDATE(startDate, months) | Returns a date shifted by a given number of months from the start date. |
| FORMAT (date) | FORMAT(table[Date], "YYYY-MM")
FORMAT(table[Date], "MMM YYYY") | Converts a date to a text string using a format mask. Use for axis labels or display columns. |
| CALENDAR | CALENDAR(DATE(2020,1,1), DATE(2025,12,31)) | Returns a single-column table of dates within a range. Used to create a custom date table. |
| CALENDARAUTO | CALENDARAUTO()
CALENDARAUTO(6) | Generates a date table automatically spanning all dates found in the model. Optional argument sets fiscal year end month. |
| EXACT | EXACT(text1, text2) | Compares two strings and returns TRUE if they are identical. Case-sensitive — unlike the = operator which ignores case. |
| CONCATENATE | CONCATENATE(text1, text2) | Joins exactly two text strings. For more than two, use the & operator. |
| CONCATENATEX | CONCATENATEX(table, expression, delimiter) | Iterates a table, evaluates an expression per row, and joins the results into a single string with a delimiter. |
| LEFT / RIGHT | LEFT(text, num_chars)
RIGHT(text, num_chars) | Extracts a given number of characters from the start or end of a string. |
| MID | MID(text, start_position, num_chars) | Extracts characters from the middle of a string. Position is 1-based. |
| LEN | LEN(text) | Returns the number of characters in a string, including spaces. |
| FIND | FIND(find_text, within_text, start_pos, error_val) | Finds one string within another and returns its position. Case-sensitive. Use SEARCH for case-insensitive. |
| SEARCH | SEARCH(find_text, within_text, start_pos, error_val) | Same as FIND but case-insensitive and supports wildcards (* and ?). |
| SUBSTITUTE | SUBSTITUTE(text, old_text, new_text) | Replaces all occurrences of a substring within a string. |
| REPLACE | REPLACE(old_text, start, num_chars, new_text) | Replaces characters at a specific position rather than by matching a substring. |
| UPPER / LOWER | UPPER(text)
LOWER(text) | Converts a string to all uppercase or all lowercase. |
| TRIM | TRIM(text) | Removes leading and trailing spaces and reduces internal multiple spaces to one. |
| VALUE | VALUE("1,234.56") | Converts a text string representing a number into a numeric value. |
| FORMAT (text) | FORMAT([Amount], "$#,##0.00")
FORMAT([Pct], "0.0%") | Formats a numeric or date value as a text string using a format mask. Always returns text — avoid in measures that will be aggregated. |
| RELATED | RELATED(relatedTable[column]) | Follows a many-to-one relationship from the current row's table to a related table and returns a column value. Used in calculated columns. |
| RELATEDTABLE | RELATEDTABLE(relatedTable) | Returns a table of all rows in a related table that are related to the current row. The inverse of RELATED. |
| TREATAS | TREATAS(table_expression, column1, column2, ...) | Applies the values of a table expression as a filter on specified columns — creates a virtual relationship without a physical model relationship. |
| USERELATIONSHIP | CALCULATE(
SUM(Sales[Amount]),
USERELATIONSHIP(Sales[ShipDate], Dates[Date])
) | Activates an inactive relationship for the duration of a CALCULATE expression. Use for role-playing dimensions with multiple date keys. |
| CROSSFILTER | CALCULATE(
[Measure],
CROSSFILTER(Sales[ProductID], Products[ID], BOTH)
) | Changes filter propagation direction within a CALCULATE scope. Use BOTH for bidirectional, ONEWAY for default, NONE to disable. |
| SUMMARIZE | SUMMARIZE(
table,
groupByCol1,
"AggName", expression
) | Groups a table by specified columns and adds aggregated columns. Prefer SUMMARIZECOLUMNS for measures; SUMMARIZE for column-level grouping. |
| SUMMARIZECOLUMNS | SUMMARIZECOLUMNS(
table[col1],
"Measure", [MyMeasure]
) | Groups by columns from any table and adds measure results. The recommended way to build summary tables in newer DAX versions. |
| GROUPBY | GROUPBY(
table,
table[col1],
"Name", CURRENTGROUP()
) | Groups a table by columns and allows iterating the resulting groups with CURRENTGROUP(). Unlike SUMMARIZE, can use iterator functions on each group. |
| ADDCOLUMNS | ADDCOLUMNS(
table,
"ColName", expression
) | Returns the original table with one or more additional calculated columns appended. Does not aggregate. |
| SELECTCOLUMNS | SELECTCOLUMNS(
table,
"NewName", table[Column]
) | Returns a new table with only the specified columns, optionally renamed. Like SELECT in SQL. |
| DISTINCT | DISTINCT(table[column])
DISTINCT(table) | Returns a one-column table of unique values, or a table with duplicate rows removed. |
| VALUES | VALUES(table[column]) | Returns a single-column table of unique values including a blank if there are unmatched rows from a relationship. Slightly different from DISTINCT. |
| TOPN | TOPN(n, table, orderByExpression, ASC_DESC) | Returns the top N rows of a table based on an ordering expression. Used inside CALCULATE or ADDCOLUMNS. |
| NATURALINNERJOIN | NATURALINNERJOIN(left_table, right_table) | Joins two tables on columns that share the same name, returning only rows with a match in both tables. Equivalent to SQL INNER JOIN. |
| NATURALLEFTOUTERJOIN | NATURALLEFTOUTERJOIN(left_table, right_table) | Joins two tables on columns with the same name, keeping all rows from the left table. NULLs where no match exists in the right table. |
| UNION | UNION(table1, table2) | Combines two tables with the same column structure, keeping all rows including duplicates. |
| INTERSECT | INTERSECT(table1, table2) | Returns rows from the first table that also exist in the second table. |
| EXCEPT | EXCEPT(table1, table2) | Returns rows from the first table that do not exist in the second table. |
| ROW | ROW("ColName", expression) | Creates a single-row table with named columns. Useful for creating inline scalar context. |
| GENERATESERIES | GENERATESERIES(1, 100, 1) | Returns a single-column table with a sequence of values from start to end by step. Useful for parameter tables and slicer ranges. |
| SELECTEDVALUE | SELECTEDVALUE(table[column], alternate_result) | Returns the single selected value in a column's filter context, or the alternate result if more than one value is selected. |
| HASONEVALUE | HASONEVALUE(table[column]) | Returns TRUE if exactly one value exists in the filter context for the column. Useful before calling VALUES or SELECTEDVALUE. |
| HASONEFILTER | HASONEFILTER(table[column]) | Returns TRUE if exactly one direct filter is applied to the column. |
| ISFILTERED | ISFILTERED(table[column]) | Returns true when there are direct filters on a column. |
| ISCROSSFILTERED | ISCROSSFILTERED(table[column]) | Returns true when there are cross-filters on a column applied via a related table. |
| ISINSCOPE | ISINSCOPE(table[column]) | Returns TRUE if the column is in the grouping axis of the current visual context. Use to control subtotal/grand total row behaviour. |
| ISBLANK | ISBLANK(expression) | Returns TRUE if the expression evaluates to BLANK. |
| ISNUMBER | ISNUMBER(expression) | Checks whether a value is a number or not. |
| ISTEXT | ISTEXT(expression) | Returns TRUE if the expression evaluates to a text value. |
| ISLOGICAL | ISLOGICAL(expression) | Checks whether a value is logical (TRUE or FALSE). Returns FALSE for numbers, text, or blank. |
| ISERROR | ISERROR(expression) | Returns TRUE if the expression raises any error. Wrap with IFERROR instead when you also want a fallback value. |
| NAMEOF | NAMEOF(table[column])
NAMEOF([MeasureName]) | Returns the name of a column or measure as a text string. Useful for building dynamic titles that stay correct after renaming. |
| USERPRINCIPALNAME | USERPRINCIPALNAME() | Returns the email address (UPN) of the current user. Commonly used in row-level security (RLS) filters to restrict data per user. |
| FIRSTNONBLANK | FIRSTNONBLANK(table[column], expression) | Returns the first value in the column for which the expression is non-blank, in ascending order. |
| LASTNONBLANK | LASTNONBLANK(table[column], expression) | Returns the last value in the column for which the expression is non-blank. Used for last-known-balance patterns. |
| RANKX | RANKX(
ALL(table[column]),
[Measure],
,
DESC,
Dense
) | Returns the rank of an expression against all values of a column. Tie behaviour: Skip (default) or Dense. Leave value blank to use current row. |
| TOPN | TOPN(10, Products, [Total Sales], DESC) | Returns the top N rows of a table sorted by an expression. Often wrapped in CALCULATE to evaluate a measure over those rows. |
| DIVIDE | DIVIDE(numerator, denominator, alternate_result) | Safe division — returns the alternate result (default BLANK) instead of an error when the denominator is zero. Always prefer over the / operator for measures. |
| ROUND | ROUND(number, num_digits) | Rounds a number to the specified number of decimal places. Negative num_digits rounds to the left of the decimal. |
| ROUNDUP / ROUNDDOWN | ROUNDUP(number, num_digits)
ROUNDDOWN(number, num_digits) | Always rounds away from or toward zero, regardless of standard rounding rules. |
| INT | INT(number) | Rounds a number down to the nearest integer (floor toward negative infinity). |
| TRUNC | TRUNC(number, num_digits) | Truncates toward zero without rounding — drops decimal digits. Use instead of INT for positive numbers. |
| ABS | ABS(number) | Returns the absolute (positive) value of a number. |
| MOD | MOD(number, divisor) | Returns the remainder after dividing number by divisor. |
| POWER | POWER(number, power) | Raises a number to the specified power. Equivalent to number ^ power. |
| SQRT | SQRT(number) | Returns the positive square root of a number. |
| SIGN | SIGN(number) | Returns 1 for positive numbers, -1 for negative, and 0 for zero. |
| MEDIAN | MEDIAN(table[column]) | Returns the middle value (50th percentile) of a numeric column. |
| MEDIANX | MEDIANX(table, expression) | Iterates a table, evaluates an expression per row, and returns the median of the results. |
| PERCENTILE.INC | PERCENTILE.INC(table[column], 0.9) | Returns the k-th percentile of values in a column, inclusive. 0.9 returns the 90th percentile. |
| STDEV.P / STDEV.S | STDEV.P(table[column])
STDEV.S(table[column]) | Standard deviation of a column. .P for the entire population; .S for a sample. |
| = (equal) | [Region] = "North" | Tests equality. Case-insensitive for text. Use == for strict equality that treats BLANK and 0 as different. |
| == (strict equal) | [Value] == BLANK() | Strict equality — treats BLANK and 0 as different values. Use when you need to distinguish blank from zero. |
| > / < / >= / <= | [Sales] > 1000
[Date] >= DATE(2024,1,1) | Comparison operators. Work on numbers, dates, and text (text compares alphabetically). |
| <> (not equal) | [Status] <> "Closed" | Tests that two values are not equal. Equivalent to != in other languages. |
| + - * / ^ | [Price] * [Quantity]
[Revenue] - [Cost]
2 ^ 10 | Standard arithmetic operators. Use DIVIDE() instead of / to safely handle division by zero. |
| & (concatenation) | [City] & ", " & [Country] | Concatenates text values. Preferred over CONCATENATE() when combining more than two strings. |
| && (logical AND) | [Sales] > 0 && [Region] = "North" | Inline AND condition. Preferred over the AND() function when combining multiple conditions. |
| || (logical OR) | [Status] = "A" || [Status] = "B" | Inline OR condition. Preferred over the OR() function when combining multiple conditions. |
| IN { } (list test) | Table[Color] IN { "Red", "Blue", "Gold" } | Tests whether a column value is in a literal set. Cleaner than multiple OR conditions. Also works inside CALCULATE as a filter. |