Table Manipulation Functions

Question 1

ADDCOLUMNS(table, name, expression[, name, expression]…)

Answer 1

Adds calculated columns to the given table or table expression.

Question 2

ADDMISSINGITEMS ( [showAll_columnName [, showAll_columnName [, … ] ] ], table [, groupBy_columnName [, [filterTable] [, groupBy_columnName [, [filterTable] [, … ] ] ] ] ] ] )

Answer 2

Adds rows with empty values to a table returned by SUMMARIZECOLUMNS.

Question 3

TREATAS(table_expression, column[, column[, column[,…]]]} )

Answer 3

Applies the result of a table expression as filters to columns from an unrelated table.

Question 4

UNION(table_expression1, table_expression2 [,table_expression]…)

Answer 4

Creates a union (join) table from a pair of tables.

Question 5

DETAILROWS([Measure])

Answer 5

Evaluates a Detail Rows Expression defined for a measure and returns the data.

Question 6

ROLLUPGROUP (groupBy_columnName [, groupBy_columnName [, … ] ] )

Answer 6

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.

Question 7

ROLLUP (groupBy_columnName [, groupBy_columnName [, … ] ] )

Answer 7

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.

Question 8

ROLLUPADDISSUBTOTAL ( [grandtotalFilter], groupBy_columnName, name [, [groupLevelFilter] [, groupBy_columnName, name [, [groupLevelFilter] [, … ] ] ] ] )

Answer 8

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.

Question 9

IGNORE(expression)

Answer 9

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.

Question 10

ROLLUPISSUBTOTAL ( [grandTotalFilter], groupBy_columnName, isSubtotal_columnName [, [groupLevelFilter] [, groupBy_columnName, isSubtotal_columnName [, [groupLevelFilter] [, … ] ] ] ] )

Answer 10

Pairs rollup groups with the column added by ROLLUPADDISSUBTOTAL. This function can only be used within an ADDMISSINGITEMS expression.

Question 11

NATURALLEFTOUTERJOIN(LeftTable, RightTable)

Answer 11

Performs a join of the LeftTable with the RightTable by using the Left Outer Join semantics.

Question 12

NATURALINNERJOIN(LeftTable, RightTable)

Answer 12

Performs an inner join of a table with another table.

Question 13

DATATABLE (ColumnName1, DataType1, ColumnName2, DataType2…, {{Value1, Value2…}, {ValueN, ValueN+1…}…})

Answer 13

Provides a mechanism for declaring an inline set of data values.

Question 14

DISTINCT(column)

Answer 14

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.

Question 15

CURRENTGROUP ( )

Answer 15

Returns a set of rows from the table argument of a GROUPBY expression that belong to the current row of the GROUPBY result.

Question 16

GENERATESERIES(startValue, endValue[, incrementValue])

Answer 16

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.

Question 17

SUMMARIZE (table, groupBy_columnName[, groupBy_columnName]…[, name, expression]…)

Answer 17

Returns a summary table for the requested totals over a set of groups.

Question 18

SUMMARIZECOLUMNS( groupBy_columnName [, groupBy_columnName]…, [filterTable]…[, name, expression]…)

Answer 18

Returns a summary table over a set of groups.

Question 19

DISTINCT(table)

Answer 19

Returns a table by removing duplicate rows from another table or expression.

Question 20

”{ scalarExpr1, scalarExpr2, … }
{ ( scalarExpr1, scalarExpr2, … ), ( scalarExpr1, scalarExpr2, … ), … }”

Answer 20

Returns a table of one or more columns.

Question 21

CROSSJOIN(table, table[, table]…)

Answer 21

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.

Question 22

SUBSTITUTEWITHINDEX(table, indexColumnName, indexColumnsTable, [orderBy_expression, [order][, orderBy_expression, [order]]…])

Answer 22

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.

Question 23

ROW(name, expression[[,name, expression]…])

Answer 23

Returns a table with a single row containing values that result from the expressions given to each column.

Question 24

SELECTCOLUMNS(Table, [Name], Expression, Name], …)

Answer 24

Returns a table with selected columns from the table and new columns specified by the DAX expressions.

Question 25

GENERATE(table1, table2)

Answer 25

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.

Question 26

GENERATEALL(table1, table2)

Answer 26

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.

Question 27

INTERSECT(table_expression1, table_expression2)

Answer 27

Returns the row intersection of two tables, retaining duplicates.

Question 28

EXCEPT(table_expression1, table_expression2

Answer 28

Returns the rows of the first table in the expression which do not appear in the second table.

Question 29

TOPN(N_Value, Table, OrderBy_Expression, [Order[, OrderBy_Expression, [Order]]…])

Answer 29

Returns the top N rows of the specified table.

Question 30

FILTERS(columnName)

Answer 30

Returns the values that are directly applied as filters to columnName.

Question 31

GROUPBY (table [, groupBy_columnName [, groupBy_columnName [, …]]] [, name, expression [, name, expression [, …]]])

Answer 31

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.

Question 32

VALUES(TableNameOrColumnName)

Answer 32

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.