Table manipulation Functions


These functions manipulate and return tables.

Function Description
ADDCOLUMNS Returns a table with new columns specified by the DAX expressions.
ADDMISSINGITEMS Add the rows with empty measure values back.
CROSSJOIN Returns a table that is a crossjoin of the specified tables.
CURRENTGROUP Access to the (sub)table representing current group in GroupBy function. Can be used only inside GroupBy function.
DATATABLE Returns a table with data defined inline.
DETAILROWS Returns the table data corresponding to the DetailRows expression defined on the specified Measure. If a DetailRows expression is not defined then the entire table to which the Measure belongs is returned.
DISTINCT Returns a one column table that contains the distinct (unique) values in a column, for a column argument. Or multiple columns with distinct (unique) combination of values, for a table expression argument.
EXCEPT Returns the rows of left-side table which do not appear in right-side table.
FILTERS Returns a table of the filter values applied directly to the specified column.
GENERATE The second table expression will be evaluated for each row in the first table. Returns the crossjoin of the first table with these results.
GENERATEALL The second table expression will be evaluated for each row in the first table. Returns the crossjoin of the first table with these results, including rows for which the second table expression is empty.
GENERATESERIES Returns a table with one column, populated with sequential values from start to end.
GROUPBY Creates a summary the input table grouped by the specified columns.
IGNORE Tags a measure expression specified in the call to SUMMARIZECOLUMNS function to be ignored when determining the non-blank rows.
INTERSECT Returns the rows of left-side table which appear in right-side table.
NATURALINNERJOIN Joins the Left table with right table using the Inner Join semantics.
NATURALLEFTOUTERJOIN Joins the Left table with right table using the Left Outer Join semantics.
ROLLUP Identifies a subset of columns specified in the call to SUMMARIZE function that should be used to calculate subtotals.
ROLLUPADDISSUBTOTAL Identifies a subset of columns specified in the call to SUMMARIZECOLUMNS function that should be used to calculate groups of subtotals.
ROLLUPGROUP Identifies a subset of columns specified in the call to SUMMARIZE function that should be used to calculate groups of subtotals.
ROLLUPISSUBTOTAL Pairs up the rollup groups with the column added by ROLLUPADDISSUBTOTAL.
ROW Returns a single row table with new columns specified by the DAX expressions.
SELECTCOLUMNS Returns a table with selected columns from the table and new columns specified by the DAX expressions.
SUBSTITUTEWITHINDEX Returns a table which represents the semijoin of two tables supplied and for which the common set of columns are replaced by a 0-based index column. The index is based on the rows of the second table sorted by specified order expressions.
SUMMARIZE Creates a summary of the input table grouped by the specified columns.
SUMMARIZECOLUMNS Create a summary table for the requested totals over set of groups.
TOPN Returns a given number of top rows according to a specified expression.
TOPNPERLEVEL
TOPNSKIP Retrieves a number of rows from a table efficiently, skipping a number of rows. Compared to TOPN, the TOPNSKIP function is less flexible, but much faster.
TREATAS Treats the columns of the input table as columns from other tables.For each column, filters out any values that are not present in its respective output column.
UNION Returns the union of the tables whose columns match.
VALUES When a column name is given, returns a single column table of unique values. When a table name is given, returns a table with the same columns and all the rows of the table (including duplicates) with the additional blank row if present.

Last update: Oct 19, 2020   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo

Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Iterator

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Deprecated

This parameter is deprecated and its use is not recommended.

Contribute

Want to improve the content of TOPNPERLEVEL? Did you find any issue?
Please, report it us! All submissions will be evaluated for possible updates of the content.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.