DAX Other - GROUPBY function

Description

Returns a table with a set of selected columns. Permits DAX CURRENTGROUP function to be used inside aggregation functions in the extension columns that it adds. GROUPBY attempts to reuse the data that has been grouped making it highly performant.

DAX GROUPBY function is similar to DAX SUMMARIZE function. However, GROUPBY does not do an implicit CALCULATE for any extension columns that it adds.

DAX GROUPBY function is new in Excel 2016.

Syntax

GROUPBY (<table>, [<groupBy_columnName1>], [<name>, <expression>] …)

Parameters

Sr.No. Term & Definition
1

table

Any DAX expression that returns a table of data.
2

groupBy_columnName1

The name of an existing column in the table (or in a related table), by which the data is to be grouped.

This parameter cannot be an expression.
3

name

The name given to a new column that is being added to the list of GroupBy columns, enclosed in double quotes.
4

expression

Any DAX expression that returns a single scalar value, where the expression is to be evaluated for each set of GroupBy values.

  • It can include any of the “X” aggregation functions, such as SUMX, AVERAGEX, MINX, MAXX, etc. and when one of these functions is used in this way, the table parameter (which is a table expression) can be replaced by CURRENTGROUP function. (Refer Remarks Section for details).

  • However, CURRENTGROUP function can only be used at the top level of table scans in the expression. That means,

    • ABS (SUMX (CURRENTGROUP (), [Column])) is allowed, since ABS does not perform a scan.

    • But, SUMX (<table>, SUMX (CURRENTGROUP () …)) is not allowed.

  • DAX CALCULATE function and calculated fields are not allowed in the expression

Return Value

A table with the selected columns for the groupBy_columnName parameters and the grouped by columns designated by the name parameters.

Remarks

The GROUPBY function does the following −

  • Start with the specified table (and all related tables in the “to-one” direction).

  • Create a grouping using all of the GroupBy columns (which are required to exist in the table from step 1).

  • Each group is one row in the result, but represents a set of rows in the original table.

  • For each group, evaluate the extension columns being added. Unlike the SUMMARIZE function, an implied CALCULATE is not performed, and the group is not placed into the filter context.

Parameters

  • Each column for which you define a name must have a corresponding expression. Otherwise, an error is returned.

    • The first parameter, name, defines the name of the column in the results. The second parameter, expression, defines the calculation performed to obtain the value for each row in that column.

    • Each name must be enclosed in double quotation marks.

  • groupBy_columnName must be either in a table or in a related table.

    • The function groups a selected set of rows into a set of summary rows by the values of one or more groupBy_columnName columns. One row is returned for each group.

CURRENTGROUP ()

  • CURRENTGROUP function can only be used in an expression that defines a column within the GROUPBY function.

  • CURRENTGROUP returns a set of rows from the table parameter of GROUPBY that belong to the current row of the GROUPBY result.

  • CURRENTGROUP function takes no parameters and is only supported as the first parameter to one of the following aggregation functions: AverageX, CountAX, CountX, GeoMeanX, MaxX, MinX, ProductX, StDevX.S, StDevX.P, SumX, VarX.S, VarX.P.

Example

= GROUPBY ( 
   Sales,Sales[Salesperson],Products[Product],"Total Sales",  
   SUMX (CURRENTGROUP (),[Sales Amount]) 
)
dax_functions_other.htm
Advertisements