Table Manipulation Functions Flashcards
ADDCOLUMNS(table, name, expression[, name, expression]…)
Adds calculated columns to the given table or table expression.
ADDMISSINGITEMS ( [showAll_columnName [, showAll_columnName [, … ] ] ], table [, groupBy_columnName [, [filterTable] [, groupBy_columnName [, [filterTable] [, … ] ] ] ] ] ] )
Adds rows with empty values to a table returned by SUMMARIZECOLUMNS.
TREATAS(table_expression, column[, column[, column[,…]]]} )
Applies the result of a table expression as filters to columns from an unrelated table.
UNION(table_expression1, table_expression2 [,table_expression]…)
Creates a union (join) table from a pair of tables.
DETAILROWS([Measure])
Evaluates a Detail Rows Expression defined for a measure and returns the data.
ROLLUPGROUP (groupBy_columnName [, groupBy_columnName [, … ] ] )
Modifies the behavior of the SUMMARIZE and SUMMARIZECOLUMNS functions by adding rollup rows to the result on columns defined by the the groupBy_columnName parameter. This function can only be used within a SUMMARIZE or SUMMARIZECOLUMNS expression.
ROLLUP (groupBy_columnName [, groupBy_columnName [, … ] ] )
Modifies the behavior of the SUMMARIZE function by adding rollup rows to the result on columns defined by the groupBy_columnName parameter. This function can only be used within a SUMMARIZE expression.
ROLLUPADDISSUBTOTAL ( [grandtotalFilter], groupBy_columnName, name [, [groupLevelFilter] [, groupBy_columnName, name [, [groupLevelFilter] [, … ] ] ] ] )
Modifies the behavior of the SUMMARIZECOLUMNS function by adding rollup/subtotal rows to the result based on the groupBy_columnName columns. This function can only be used within a SUMMARIZECOLUMNS expression.
IGNORE(expression)
Modifies the behavior of the SUMMARIZECOLUMNS function by omitting specific expressions from the BLANK/NULL evaluation. Rows for which all expressions not using IGNORE return BLANK/NULL will be excluded independent of whether the expressions which do use IGNORE evaluate to BLANK/NULL or not. This function can only be used within a SUMMARIZECOLUMNS expression.
ROLLUPISSUBTOTAL ( [grandTotalFilter], groupBy_columnName, isSubtotal_columnName [, [groupLevelFilter] [, groupBy_columnName, isSubtotal_columnName [, [groupLevelFilter] [, … ] ] ] ] )
Pairs rollup groups with the column added by ROLLUPADDISSUBTOTAL. This function can only be used within an ADDMISSINGITEMS expression.
NATURALLEFTOUTERJOIN(LeftTable, RightTable)
Performs a join of the LeftTable with the RightTable by using the Left Outer Join semantics.
NATURALINNERJOIN(LeftTable, RightTable)
Performs an inner join of a table with another table.
DATATABLE (ColumnName1, DataType1, ColumnName2, DataType2…, {{Value1, Value2…}, {ValueN, ValueN+1…}…})
Provides a mechanism for declaring an inline set of data values.
DISTINCT(column)
Returns a one-column table that contains the distinct values from the specified column. In other words, duplicate values are removed and only unique values are returned.
CURRENTGROUP ( )
Returns a set of rows from the table argument of a GROUPBY expression that belong to the current row of the GROUPBY result.
GENERATESERIES(startValue, endValue[, incrementValue])
Returns a single column table containing the values of an arithmetic series, that is, a sequence of values in which each differs from the preceding by a constant quantity. The name of the column returned is Value.
SUMMARIZE (table, groupBy_columnName[, groupBy_columnName]…[, name, expression]…)
Returns a summary table for the requested totals over a set of groups.
SUMMARIZECOLUMNS( groupBy_columnName [, groupBy_columnName]…, [filterTable]…[, name, expression]…)
Returns a summary table over a set of groups.
DISTINCT(table)
Returns a table by removing duplicate rows from another table or expression.
”{ scalarExpr1, scalarExpr2, … }
{ ( scalarExpr1, scalarExpr2, … ), ( scalarExpr1, scalarExpr2, … ), … }”
Returns a table of one or more columns.
CROSSJOIN(table, table[, table]…)
Returns a table that contains the Cartesian product of all rows from all tables in the arguments. The columns in the new table are all the columns in all the argument tables.
SUBSTITUTEWITHINDEX(table, indexColumnName, indexColumnsTable, [orderBy_expression, [order][, orderBy_expression, [order]]…])
Returns a table which represents a left semijoin of the two tables supplied as arguments. The semijoin is performed by using common columns, determined by common column names and common data type . The columns being joined on are replaced with a single column in the returned table which is of type integer and contains an index. The index is a reference into the right join table given a specified sort order.
ROW(name, expression[[,name, expression]…])
Returns a table with a single row containing values that result from the expressions given to each column.
SELECTCOLUMNS(Table, [Name], Expression, Name], …)
Returns a table with selected columns from the table and new columns specified by the DAX expressions.
GENERATE(table1, table2)
Returns a table with the Cartesian product between each row in table1 and the table that results from evaluating table2 in the context of the current row from table1.
GENERATEALL(table1, table2)
Returns a table with the Cartesian product between each row in table1 and the table that results from evaluating table2 in the context of the current row from table1.
INTERSECT(table_expression1, table_expression2)
Returns the row intersection of two tables, retaining duplicates.
EXCEPT(table_expression1, table_expression2
Returns the rows of the first table in the expression which do not appear in the second table.
TOPN(N_Value, Table, OrderBy_Expression, [Order[, OrderBy_Expression, [Order]]…])
Returns the top N rows of the specified table.
FILTERS(columnName)
Returns the values that are directly applied as filters to columnName.
GROUPBY (table [, groupBy_columnName [, groupBy_columnName [, …]]] [, name, expression [, name, expression [, …]]])
The GROUPBY function is similar to the SUMMARIZE function. However, GROUPBY does not do an implicit CALCULATE for any extension columns that it adds. GROUPBY permits a new function, CURRENTGROUP, to be used inside aggregation functions in the extension columns that it adds. GROUPBY is used to perform multiple aggregations in a single table scan.
VALUES(TableNameOrColumnName)
When the input parameter is a column name, returns a one-column table that contains the distinct values from the specified column. Duplicate values are removed and only unique values are returned. A BLANK value can be added. When the input parameter is a table name, returns the rows from the specified table. Duplicate rows are preserved. A BLANK row can be added.
