This reference lists 24 DAX table manipulation functions, with frequently used functions marked 🔥. Each function includes syntax, an example and parameter notes.

Function Description Syntax Example
ADDCOLUMNS 🔥 Adds calculated columns to a given table ADDCOLUMNS(<table>, <name>, <expression>) ADDCOLUMNS(Sales, "Total", Sales[Amount] * Sales[Quantity])
DISTINCT 🔥 Returns the distinct values of a column DISTINCT(<column>) DISTINCT(Sales[Region])
SUMMARIZE 🔥 Returns a summary table grouped by the specified columns SUMMARIZE(<table>, <groupBy_columnName>, [<groupBy_columnName>], <name>, <expression>) SUMMARIZE(Sales, Sales[Region], "Total", SUM(Sales[Amount]))
SUMMARIZECOLUMNS 🔥 Returns a summary table without relying on the calling context SUMMARIZECOLUMNS(<column>, [<groupBy_column>], <name>, <expression>) SUMMARIZECOLUMNS(Sales[Region], "Total", SUM(Sales[Amount]))
TREATAS 🔥 Applies the result of a table expression as filters on related columns TREATAS(<expression>, <columnName>, [<columnName>]...) CALCULATE(SUM(Sales[Amount]), TREATAS({"North", "South"}, Sales[Region]))
UNION 🔥 Returns the union of two tables UNION(<table1>, <table2>) UNION(FirstQuarterSales, SecondQuarterSales)
VALUES 🔥 Returns the distinct values of a column, or the unique values in the current context VALUES(<column>) VALUES(Sales[Region])
CROSSJOIN Returns the Cartesian product of two tables CROSSJOIN(<table1>, <table2>) CROSSJOIN(Regions, Products)
DATATABLE Defines an inline table DATATABLE(<column1>, <type1>, <column2>, <type2>, {{<value1>, <value2>}, ...}) DATATABLE("Region", STRING, "Sales", INTEGER, {{"North", 1000}, {"South", 2000}})
EXCEPT Returns rows of the left table that do not appear in the right table EXCEPT(<table_expression1>, <table_expression2>) EXCEPT(AllSales, ReturnedSales)
FIRST Gets the value of the first row of a matrix, in visual calculations FIRST(<expression>) FIRST(SUM(Sales[Amount]))
GENERATE Evaluates the right table expression for each row of the left table GENERATE(<leftTable>, <rightTableExpression>) GENERATE(Regions, RELATEDTABLE(Sales))
GENERATEALL Evaluates the right table expression for each row of the left table, including rows with no match GENERATEALL(<leftTable>, <rightTableExpression>) GENERATEALL(Regions, RELATEDTABLE(Sales))
GROUPBY An enhancement of SUMMARIZE that supports computations after grouping GROUPBY(<table>, <groupBy_columnName>, <name>, <expression>) GROUPBY(Sales, Sales[Region], "Total", SUMX(CURRENTGROUP(), Sales[Amount]))
INTERSECT Returns the intersection of two tables INTERSECT(<table_expression1>, <table_expression2>) INTERSECT(FirstQuarterSales, SecondQuarterSales)
LAST Gets the value of the last row of a matrix, in visual calculations LAST(<expression>) LAST(SUM(Sales[Amount]))
MATCHBY Defines, inside window functions, the columns used to match data and identify the current row MATCHBY(<column>[, <column>...]) RANKX(MATCHBY(Sales[Region], Sales[Product]), SUM(Sales[Amount]))
NATURALINNERJOIN Performs an inner join based on common columns between two tables NATURALINNERJOIN(<leftTable>, <rightTable>) NATURALINNERJOIN(Sales, Products)
NATURALLEFTOUTERJOIN Performs a left outer join based on common columns between two tables NATURALLEFTOUTERJOIN(<leftTable>, <rightTable>) NATURALLEFTOUTERJOIN(Sales, Products)
NEXT Gets the value of the next row, in visual calculations NEXT(<expression>) NEXT(SUM(Sales[Amount]))
PREVIOUS Gets the value of the previous row, in visual calculations PREVIOUS(<expression>) PREVIOUS(SUM(Sales[Amount]))
ROW Returns a single-row table ROW(<name>, <expression>, [<name>, <expression>]) ROW("Total", 100, "Count", 10)
SELECTCOLUMNS Returns a table containing only the specified columns SELECTCOLUMNS(<table>, <name>, <scalar_expression>) SELECTCOLUMNS(Sales, "Region", Sales[Region], "Amount", Sales[Amount])
TABLE Creates a table expression TABLE(<expression>) TABLE(Sales)

Detailed parameters for frequently used functions

ADDCOLUMNS

Adds calculated columns to a given table.

Parameter Description
table The table to add calculated columns to.
name The name of the new column (a string).
expression The expression that computes the new column's values.

DISTINCT

Returns the distinct values of a column.

Parameter Description
column The column to deduplicate.

SUMMARIZE

Returns a summary table grouped by the specified columns.

Parameter Description
table The table to group.
groupBy_columnName The column used for grouping.
name The name of the aggregated column.
expression The aggregation expression.

SUMMARIZECOLUMNS

Returns a summary table without relying on the calling context.

Parameter Description
column The column to return.
groupBy_column (Optional) The column used for grouping.
name The name of the aggregated column.
expression The aggregation expression.

TREATAS

Applies the result of a table expression as filters on related columns.

Parameter Description
expression A table expression.
columnName The target column name. There can be multiple.

UNION

Returns the union of two tables.

Parameter Description
table1 The first table.
table2 The second table. More table arguments are allowed.

VALUES

Returns the distinct values of a column, or the unique values in the current context.

Parameter Description
column The column whose values are returned.

From Online Toolbox (www.vba.net)