Power BI GROUPBY Function Guide

The GROUPBY function in DAX creates a summary table by grouping rows from a source table and applying aggregation expressions using the CURRENTGROUP() function. Unlike SUMMARIZE, GROUPBY operates at the row level without context transitions, making it a precise tool for scenarios requiring explicit control over aggregation behavior in Power BI models.

GROUPBY Syntax and Parameters

GROUPBY returns a table with grouped columns and aggregated values. The key distinction is that aggregation expressions must use CURRENTGROUP() rather than standard CALCULATE-based measures.

GROUPBY (
    <Table>,
    [<GroupBy_ColumnName1>],
    [<GroupBy_ColumnName2>],
    [<Name>], [<Expression>],
    ...
)

-- Example: Group sales by region with total amount
EVALUATE
GROUPBY (
    Sales,
    Sales[Region],
    "Total Sales", SUMX ( CURRENTGROUP (), Sales[Amount] ),
    "Order Count", COUNTX ( CURRENTGROUP (), Sales[OrderID] )
)

• Table – The source table to group. Can be a physical table, calculated table, or table expression.
• GroupBy columns – One or more columns to group by. These columns define the granularity of the output table.
• Name/Expression pairs – New column names and their aggregation expressions. Expressions MUST use CURRENTGROUP() as the iterator table.
• CURRENTGROUP() – A special function that returns the set of rows in the current group, used exclusively within GROUPBY aggregation expressions.

GROUPBY vs SUMMARIZE: Key Differences

GROUPBY and SUMMARIZE both create summary tables, but they differ fundamentally in how they evaluate aggregation expressions. Understanding these differences is critical for writing correct and performant DAX.

• Context transitions – SUMMARIZE performs context transitions when evaluating expressions, which can produce unexpected results with measures. GROUPBY does NOT perform context transitions.
• CURRENTGROUP vs measures – GROUPBY requires CURRENTGROUP() in aggregation expressions. SUMMARIZE allows referencing measures directly but is prone to errors due to implicit context transitions.
• Related table grouping – SUMMARIZE can group by columns from related tables. GROUPBY can only group by columns from the base table or columns already present in the table expression.
• Microsoft recommendation – Microsoft recommends using SUMMARIZECOLUMNS for new development, GROUPBY for explicit row-level aggregation, and avoiding SUMMARIZE with expressions.

-- SUMMARIZE (avoid using expressions directly)
EVALUATE
SUMMARIZE (
    Sales,
    Sales[Region],
    "Total", SUM ( Sales[Amount] )  -- Works but uses context transition
)

-- GROUPBY (explicit, no context transition)
EVALUATE
GROUPBY (
    Sales,
    Sales[Region],
    "Total", SUMX ( CURRENTGROUP (), Sales[Amount] )
)

-- SUMMARIZECOLUMNS (recommended for most scenarios)
EVALUATE
SUMMARIZECOLUMNS (
    Sales[Region],
    "Total", SUM ( Sales[Amount] )
)

Practical GROUPBY Examples

GROUPBY excels in scenarios where you need precise control over aggregation logic, particularly when working with complex expressions that could produce incorrect results with SUMMARIZE's implicit context transitions.

-- Average order value by customer segment
EVALUATE
GROUPBY (
    Sales,
    Sales[CustomerSegment],
    "Avg Order Value", AVERAGEX ( CURRENTGROUP (), Sales[Amount] ),
    "Max Order", MAXX ( CURRENTGROUP (), Sales[Amount] ),
    "Min Order", MINX ( CURRENTGROUP (), Sales[Amount] ),
    "Distinct Products", COUNTX (
        DISTINCT ( SELECTCOLUMNS ( CURRENTGROUP (), "P", Sales[ProductID] ) ),
        [P]
    )
)

-- Weighted average price by category
EVALUATE
GROUPBY (
    Products,
    Products[Category],
    "Weighted Avg Price", DIVX (
        SUMX ( CURRENTGROUP (), Products[Price] * Products[UnitsSold] ),
        SUMX ( CURRENTGROUP (), Products[UnitsSold] )
    )
)

• Multi-aggregation – Calculate SUM, AVERAGE, MIN, MAX, and COUNT in a single GROUPBY call for comprehensive summaries
• Weighted averages – CURRENTGROUP makes it straightforward to compute weighted calculations within each group
• Distinct counts within groups – Count unique values within each group using DISTINCT + SELECTCOLUMNS over CURRENTGROUP()
• Conditional aggregation – Use IF or FILTER within CURRENTGROUP() expressions to aggregate only rows meeting specific conditions

Performance Optimization for GROUPBY

GROUPBY performance depends on the source table size and the complexity of aggregation expressions. These optimization techniques ensure your GROUPBY queries remain responsive in enterprise-scale models.

• Pre-filter the source table – Use FILTER or CALCULATETABLE to reduce the row count before passing to GROUPBY
• Limit grouping columns – Each additional grouping column increases the number of output rows and processing time
• Avoid nested GROUPBY – Do not nest GROUPBY inside another GROUPBY; use a single call with all required grouping columns
• Use in calculated tables – GROUPBY is most commonly used in EVALUATE queries (DAX Studio) or calculated tables, not in measures
• Consider SUMMARIZECOLUMNS – For standard aggregation scenarios, SUMMARIZECOLUMNS is typically faster than GROUPBY due to engine optimizations

Why Choose EPC Group for Power BI DAX Expertise

EPC Group's DAX specialists have optimized enterprise Power BI models for 29 years as a Microsoft Gold Partner. Our founder, Errin O'Connor, authored 4 bestselling Microsoft Press books including the definitive Power BI guide. We bring deep expertise in advanced DAX patterns including GROUPBY, SUMMARIZECOLUMNS, and custom aggregation logic for Fortune 500 organizations where data accuracy and query performance are mission-critical.

Frequently Asked Questions