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)