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. |
| NONVISUAL | Mark the filter as NonVisual. |
| 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. |
| SAMPLEAXISWITHLOCALMINMAX |
Returns a subset from a table expression that is obtained by binning the primary X-Axis into equal-sized bins and then preserving the local min/max for each bin across different series.
|
| 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 caused by an invalid relationship if present. |
Last update: Apr 27, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber
2018-2024 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us » Privacy Policy & Cookies