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.
