Introduction
Today I am going to explain one more category of DAX functions i.e. table manipulation function. These table manipulation functions return tables and these functions are used to manipulate the table like ADDCOLUMN, SELECT, ROW etc.
Functions and Their Description
ADDCOLUMNS
Returns a table with new columns specified by the DAX expression.
Syntax = ADDCOLUNM (Table, Name1, Expression1,....)
Table - Any DAX expression that returns a table of data.
Name - The name given to the column, enclosed in double quotes.
Expression -Any DAX expression that returns a scalar expression, evaluated for each row of table.
ADDMISSINGITEMS
Add the row with empty measure value back.
Syntax – ADDMISSINGITEMS ([ShowAll_ColumnName1],.., Table, [GroupBy_ColumnName1], …, [FilterTable1],…)
ShowAll_ColumnName1 –It is Optional. A column for which to return items with no data for the measures used. If not specified, all columns are returned.
Table –Any existing table.
GroupBy_ColumnName1 -A column to group by in the supplied table argument. It is optional.
FilterTable1 –Any expression that defines which rows are returned. It is also optional.
CROSSJOIN
Returns a table that is a crossjoin of the specified tables.
Syntax – CROSSJOIN (Table1, ….)
Tables – Any expression that returns a table or tables to perform joins.
DATATABLE
Returns a table with data defined inline.
Syntax –DATATABLE (Name1, Type1, …., data)
Name1 – Any column or expression that returns table.
Type1 – Data type can be INTEGER, DOUBLE, STRING, BOOLEAN, CURRENCY, DATETIME
Data – Set of data value present in an existing table.
EXCEPT
Returns the rows of left-side table which do not appear in right-side table.
Syntax – EXCEPT (LeftTable, RightTable)
LeftTable – Any expression that returns a table.
RightTable - Any expression that returns a table.
FILTERS
Returns a table of the filter value applied directly to the specified column.
Syntax – FILTERS (ColumnName)
ColumnName - The name of an existing column, using standard DAX syntax. It cannot be an expression.
GENERATE
The second table expression will be evaluated for each row in the first table. Returns the cross join of the first table with these results.
Syntax – GENERATE (Table1, Table2)
Table1 – Any expression that returns table.
Table2 – Any expression that returns table.
GENERATEALL
The second table expression will be evaluated for each row in the first table. Returns the cross join of the first table with these results, including rows for which the second table expression is empty.
Syntax – GENERATEALL (Table1, Table2)
Table1 – Any expression that returns table.
Table2 – Any expression that returns table.
GENERATESERIES
Returns a table with one column, populated with sequential values from start to end.
Syntax – GENERATESSERIES (StartValue, EndValue, [IncrementValue])
StartValue - The initial value used to generate the sequence.
EndValue - The end value used to generate the sequence.
IncrementValue – It is optional, default value is 1.
GROUPBY
Creates a summary the input table grouped by the specified columns.
Syntax – GROUPBY (Table, [Groupby_columnName1], … , [Name1], [Expression1],…..)
Table -Any DAX expression that returns a table of data.
GroupBy_columnName - The name of an existing column in the table (or in a related table,) by which the data is to be grouped.
Name - The name given to a new column that is being added to the list of GroupBy columns, enclosed in double quotes.
Expression - One of the X aggregation functions with the first argument being CURRENTGROUP().
UNION
Returns the union of the tables whose columns match.
Syntax – UNION (Table1, …)
Table –Any expression that returns table.
VALUES
When a column name is given, it returns a single column table of unique values. When a table name is given, it returns a table with the same columns.
Syntax –VALUES (TableNameOrColumnName)
TableNameOrColumnName–Any existing table or column