DAX SUMMARIZECOLUMNS function is categorized under Filter functions. SUMMARIZECOLUMNS, is a replacement of SUMMARIZE and does not require the use of ADDCOLUMNS .
Purpose of DAX SUMMARIZECOLUMNS Function
The SUMMARIZECOLUMNS helps to get a table which includes combinations of values from the supplied columns, based on the grouping specified. The table which will be returned will include only rows for which at least one of the supplied expressions return a non-blank value. A row is not included in the table returned, if all expressions evaluate to BLANK/NULL for a row.
The DAX SUMMARIZECOLUMNS function returns a summary table over a set of groups.
A fully qualified column reference (Table[Column]) to a base table for which the distinct values are included in the returned table. Each groupBy_columnName column is cross-joined (different tables) or auto-existed (same table) with the subsequent specified columns.
The table expression which is added to the filter context of all columns specified as groupBy_columnName arguments. The values present in the filter table are used to filter before cross-join/auto-exist is performed.
A string representing the column name to use for the subsequent expression specified.
Any DAX expression that returns a single value (not a table).
SUMMARIZECOLUMNS does not guarantee any order of sorting for the results.
In the groupBy_columnName parameter, a column cannot be specified more than one time.
DAX SUBSTITUTEWITHINDEX Function is categorized under Filter functions. The DAX SUBSTITUTEWITHINDEX function can replace those columns in a row set corresponding to column headers of a matrix by indexes representing their positions.
Purpose of DAX SUBSTITUTEWITHINDEX Function
The DAX 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.
The DAX SUBSTITUTEWITHINDEX function returns a table which represents a left semijoin of the two tables supplied as arguments.
A table to be filtered by performing a left semijoin with the table specified as the third argument (indexColumnsTable). This is the table on the left side of the left semijoin so the table returned includes the same columns as this table except that all common columns of the two tables will be replaced with a single index column in the table returned.
A string which specifies the name of the index column which is replacing all the common columns in the two tables supplied as arguments to this function.
The second table for the left semijoin. This is the table on the right side of the left semijoin. Only values present in this table will be returned by the function. Also, the columns of this table (based on column names) will be replaced with a single index column in the table returned by this function.
Any DAX expression where the result value is used to specify the desired sort order of the indexColumnsTable table for generating correct index values. The sort order specified for the indexColumnsTable table defines the index of each row in the table and that index is used in the table returned to represent combinations of values in the indexColumnsTable as they appear in the table supplied as the first argument to this function.
A value that specifies how to sort orderBy_expression values, ascending or descending:
Value: Desc. Alternative value: 0(zero)/FALSE. Sorts in descending order of values of orderBy_expression. This is the default value when order parameter is omitted.
Value: ASC. Alternative value: 1/TRUE. Ranks in ascending order of orderBy_expression.
The number of rows to return as a sample. It is any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context). If a non-integer value (or expression) is entered, the result is cast as an integer.
Any DAX expression that returns a table of data from where to extract the n_value number of rows.
Any scalar DAX expression where the result value is evaluated for each row of table.
A value that specifies how to sort orderBy_expression values. 0/FALSE: Sorts in descending order of values of orderBy_expression. 1/TRUE: Sorts in ascending order of values of orderBy_expression. If omitted, default is 0.
SAMPLE returns an empty table, If n_value is 0 (zero) or less.
The table provided as the second argument should be grouped by the column used for sorting to avoid duplicate value in the sample.
If no ordering is specified, the sample will be random, not stable, and not deterministic.
DAX ADDMISSINGITEMS function is categorized under Filter functions. Wherein this function adds combinations of items from various columns to a table if it doesn’t exist. The classification of which item to be combined is based on referencing source columns which contain all the possible values for the columns.
To define the combinations of items from different columns to evaluate AutoExist function is applied for columns within the same table while CrossJoin is applied across different tables.
The ADDMISSINGITEMS function will return BLANK values for the IsSubtotal columns of blank rows it adds.
Purpose of DAX ADDMISSINGITEMS Function
This function used to add missing items from other columns.
This function returns a table that has a combination of missing items.
DAX ROLLUP Function is categorized under Filter functions. The ROLLUP function is an add-on to the Summarize function, where Summarize aggregates data based on some grouping, ROLLUP gives sub-totals, and a grand total. RollUp shows aggregated data (count, sum) of the grouped by columns first then overall aggregated data (count, sum) without any grouping and so on.
Purpose of DAX ROLLUP Function
The ROLLUP function is used as an argument inside the Summarize function.
The ROLLUP function returns does not return a value. It only specifies the set of columns to be subtotaled.