Tag

DAX FILTER Functions

Browsing

DAX SUMMARIZECOLUMNS function is categorized under Filter functions. SUMMARIZECOLUMNS, is a replacement of SUMMARIZE and does not require the use of ADDCOLUMNS .

DAX-SUMMARIZECOLUMNS-Function

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.

Return value

The DAX SUMMARIZECOLUMNS function returns a summary table over a set of groups.

Syntax

= SUMMARIZECOLUMNS( <groupBy_columnName> [, < groupBy_columnName >]…, [<filterTable>]…[, <name>, <expression>]…)

Arguments

ArgumentsDescriptionRemarks
groupBy_columnName 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. Mandantory
filterTable 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. Mandatory

name
A string representing the column name to use for the subsequent expression specified. Mandatory
expression Any DAX expression that returns a single value (not a table). Mandatory

Usage notes

  • 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 SUMMARIZE Function is categorized under Filter functions. This function can be done with ROLLUP function also.

DAX-SUMMARIZE-Function

Purpose of DAX SUMMARIZE Function

The SUMMARIZE helps to get a table with the selected columns for the groupBy_columnName parameters and the summarized columns designed by the name parameters.

Return value

The DAX SUMMARIZE function returns a summary table for the requested totals over a set of groups.

Syntax

= SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)

Arguments

ArgumentsDescriptionRemarks

table
Any DAX expression that returns a table of data. Mandantory
groupbyColumnName The qualified name of an existing column to be used to create summary groups based on the values found in it. This parameter cannot be an expression. Optional

name
The name given to a total or summarize column, enclosed in double quotes. Mandatory
expression Any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context). Mandatory

Usage notes

  • An error is returned if each column for which you define a name does not have a corresponding expression. The first parameter, ‘name’ defines the name of the column in the results.
  • The second argument, ‘expression’ defines the calculation performed to obtain the value for each row in that column.
  • groupBy_columnName must be either in a table or in a related table to table.
  • Each name must be enclosed in double quotation marks.
  • One row is returned for each group.

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.

DAX-SUBSTITUTEWITHINDEX-Function

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.

Return value

The DAX SUBSTITUTEWITHINDEX function returns a table which represents a left semijoin of the two tables supplied as arguments.

Syntax

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

Arguments

ArgumentsDescriptionRemarks

table

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.
Mandantory
indexColumnName 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. Mandantory

indexColumnsTable
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. Mandatory

orderBy_expression
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. Mandatory
order  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.
Optional

DAX SAMPLE Function is categorized under Filter functions. This function returns a sample subset from a given table expression.

DAX-SAMPLE-Function

Purpose of DAX SAMPLE Function

The DAX SAMPLE function helps to get a table consisting of a sample of N rows of table or an empty table if n_value is 0 (zero) or less. 

Return value

The SAMPLE function returns a sample of N rows from the specified table.

Syntax

= SAMPLE (<n_value> , <table> , <orderBy_expression> , [<order>] , [< orderBy_expression >, <order.]] .. )

Arguments

ArgumentsDescriptionRemarks

n_value

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.
Mandantory

table
Any DAX expression that returns a table of data from where to extract the n_value number of rows. Mandantory

orderBy_expression
Any scalar DAX expression where the result value is evaluated for each row of table. Optional

order
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.
Optional

Usage notes

  • 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 ROW Function is categorized under Filter functions. In this function the arguments must always come in pairs of name and expression.

DAX-ROW-Function

Purpose of DAX ROW Function

The ROW function helps to get a table with a single row containing values that result from the expressions given to each column.

Return value

The ROW function returns a single row table.

Syntax

= ROW(<name>, <expression>[[,<name>, <expression>]…])

Arguments

ArgumentsDescriptionRemarks
name
The name given to the column, enclosed in double quotes.
Mandatory
expression Any DAX expression that returns a single scalar value to populate. name. Mandantory

DAX ROLLUPISSUBTOTALFunction is categorized under Filter functions.

DAX-ROLLUPISSUBTOTAL-Function

Purpose of DAX ROLLUPISSUBTOTAL Function

The DAX ROLLUPISSUBTOTAL function pairs up the rollup groups with the column added by ROLLUPADDISSUBTOTAL.

Return value

The ROLLUPISSUBTOTAL function does not return a value. It only marks a subset of columns to ADDMISSINGITEMS .

Syntax

= ROLLUPISSUBTOTAL ( [<GrandtotalFilter>], <GroupBy_ColumnName>, <IsSubtotal_ColumnName> [, [<GroupLevelFilter>] [, <GroupBy_ColumnName>, <IsSubtotal_ColumnName> [, [<GroupLevelFilter>] [, … ] ] ] ] )

Arguments

ArgumentsDescriptionRemarks
GrandtotalFilter
Filter to be applied to the grandtotal level.
Optional
GroupBy_ColumnName A column to be returned. Mandantory
IsSubtotal_ColumnName An added Is subtotal column. Mandantory
GroupLevelFilter
Filter to be applied to the current level.
Optional

DAX ROLLUPGROUP Function is categorized under Filter functions. The ROLLUPGROUP function is an add-on to the Summarize or Addmissingitems function.

DAX-ROLLUPGROUP-Function

Purpose of DAX ROLLUPGROUP Function

The ROLLUPGROUP function identifies a subset of columns specified in the call to SUMMARIZE function that should be used to calculate groups of subtotals.

Return value

The ROLLUPGROUP function does not return a value. It only specifies the set of columns to be subtotaled.

Syntax

= ROLLUPGROUP ( <GroupBy_ColumnName> [, <GroupBy_ColumnName> [, … ] ] )

Arguments

ArgumentsDescriptionRemarks
GroupBy_ColumnName A column to be returned. Mandantory

Usage notes

  • ROLLUPGROUP can be used to calculate groups of subtotals.
  • If used within SUMMARIZE in-place of ROLLUP, ROLLUPGROUP will yield the same result by adding roll-up rows to the result on the GroupBy_ColumnName columns.
  • However, the addition of ROLLUPGROUP() inside a ROLLUP syntax can be used to prevent partial subtotals in roll-up rows.

DAX ROLLUPADDISSUBTOTAL Function is categorized under Filter functions. The ROLLUP function is an add-on to the Summarizecolumns function.

DAX-ROLLUPADDISSUBTOTAL-Function

Purpose of DAX ROLLUPADDISSUBTOTAL Function

The ROLLUPADDISSUBTOTAL function is used exclusively within SUMMARIZECOLUMNS.

Return value

The ROLLUPADDISSUBTOTAL function returns does not return a value. It only specifies the set of columns to be subtotaled.

Syntax

= ROLLUPADDISSUBTOTAL ( [<GrandtotalFilter>], <GroupBy_ColumnName>, <Name> [, [<GroupLevelFilter>] [, <GroupBy_ColumnName>, <Name> [, [<GroupLevelFilter>] [, … ] ] ] ] )

Arguments

ArgumentsDescriptionRemarks
GrandtotalFilter Filter to be applied to the grandtotal level. Optional
GroupBy_ColumnName A column to be returned. Mandatory
Name A column name to be added. Mandatory
GroupLevelFilter Filter to be applied to the current level. Optional

Usage notes

  • The addition of the ROLLUPADDISSUBTOTAL() syntax modifies the behavior of the SUMMARIZECOUMNS function by adding roll-up/subtotal rows to the result based on the groupBy_columnName columns.

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.

DAX-ADDMISSING-Function

Purpose of DAX ADDMISSINGITEMS Function

This function used to add missing items from other columns.

Return value

This function returns a table that has a combination of missing items.

Syntax

=ADDMISSINGITEMS ( [<ShowAll_ColumnName> [, <ShowAll_ColumnName> [, … ] ] ], <Table> [, <GroupBy_ColumnName> [, [<FilterTable>] [, <GroupBy_ColumnName> [, [<FilterTable>] [, … ] ] ] ] ] ] )

Arguments

ArgumentsDescriptionRemarks
ShowAll_ColumnName ShowAll columns. Mandatory
Table A SummarizeColumns table. Mandatory
GroupBy_ColumnName A column to group by or a call to ROLLUP function and ISSUBTOTALCOLUMNS function to specify a list of columns to group by with subtotals. Mandatory
FilterTable An expression that defines the table from which rows are to be returned. Mandatory

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.

DAX-ROLLUP-Function

Purpose of DAX ROLLUP Function

The ROLLUP function is used as an argument inside the Summarize function. 

Return value

The ROLLUP function returns does not return a value. It only specifies the set of columns to be subtotaled.

Syntax

= ROLLUP ( <GroupBy_ColumnName> [, <GroupBy_ColumnName> [, … ] ] )

Arguments

ArgumentsDescriptionRemarks
GroupBy_ColumnNameThe GroupBy_ColumnName parameter must be a qualified name of an existing column to be used to create summary groups based on the values found in it.The parameter cannot be an expression. Mandatory

Usage notes

  • The columns mentioned in the ROLLUP expression cannot be referenced as part of a GroupBy_columnName columns in SUMMARIZE.