Dax Function: SUMMARIZECOLUMNS

Category: Table Manipulation Functions

The SUMMARIZECOLUMNS function in Power BI is a high-performance DAX function designed to create a summary table by grouping data based on specified columns. Unlike its predecessor, SUMMARIZE, this function is optimized for speed and flexibility in summarization tasks, especially with large datasets.

Purpose

  • Efficient Summarization: Creates aggregated summaries with better performance than older functions like SUMMARIZE.

  • Conditional Aggregation: Allows integration with filters to produce context-aware results.

  • Custom Reporting: Builds dynamic summary tables for dashboards and reports.

Type of Calculations

  • Groups data by specified columns.

  • Computes aggregated metrics or values for each group.

  • Supports integration with filtering for targeted summarization.

Practical Use Cases

  1. Optimized Data Summaries: Efficiently summarize sales, revenue, or other metrics.

  2. Dynamic Dashboards: Create calculated tables based on user-selected filters.

  3. Export Aggregated Data: Prepare grouped datasets for downstream analysis or sharing.


SUMMARIZECOLUMNS (
<groupby_columnname1>, <groupby_columnname2>, ...,
[Filter1], [Filter2], ...,
<name1>, <expression1>, <name2>, <expression2>, ...
)</expression2></name2></expression1></name1></groupby_columnname2></groupby_columnname1>
ParameterTypeDescription
GroupBy_ColumnNameColumnOne or more columns used to group data.
FilterFilterOptional filter expressions to apply before summarization.
NameStringName of a new column in the resulting table.
ExpressionDAX ExpressionAggregation or calculation expression for the new column.

How Does SUMMARIZE Dax Works

The SUMMARIZECOLUMNS function performs the following steps:

  1. Groups rows in the specified table(s) based on the provided column names.

  2. Applies any filters provided in the function.

  3. Computes aggregated metrics for the grouped data using specified DAX expressions.

Logical Principle

Given an input table:

Region | Product | Sales 
 North |    A    | 100   
 North |    B    | 200   
 South |    A    | 150   

Using SUMMARIZECOLUMNS:

SUMMARIZECOLUMNS ( Sales[Region], "Total Sales", SUM(Sales[Sales]) )

Result:

Region | Total Sales
North  |   300        
South  |   150        

What Does It Return?

  • Type: Table.

  • Content: A new table grouped by specified columns, with optional calculated columns and applied filters.

When Should We Use It?

  • To create summarized tables dynamically, considering user selections or slicers.

  • When working with large datasets where performance is critical.

  • For advanced reporting scenarios requiring conditional aggregation.

Examples

Basic Usage :

Group Data Without Filters:


SUMMARIZECOLUMNS (
Sales[Region],
"Total Sales", SUM(Sales[Sales])
)

Result:

Region | Total Sales
———|————
North  |    300
South  |    150

Column Usage

Group by Region and Product:


SUMMARIZECOLUMNS (
Sales[Region], Sales[Product],
"Total Sales", SUM(Sales[Sales])
)

Result:

Region | Product | Total Sales
North   |        A      | 100
North   |        B      | 200
South   |        A      | 150

Advanced Usage

Combining with Dynamic Filters:


SUMMARIZECOLUMNS (
Sales[Region],
Sales[Product],
KEEPFILTERS ( Sales[Sales] &gt; 100 ),
"Filtered Sales", SUM(Sales[Sales])
)

Tips and Tricks

  • Use SUMMARIZECOLUMNS instead of SUMMARIZE for better performance.

  • Combine with filtering functions like FILTER or KEEPFILTERS for dynamic results.

  • Avoid using SUMMARIZECOLUMNS directly in measures; it is more suited for calculated tables.

  • Be cautious when using overlapping filters that can cause unexpected results.

Performance Impact of SUMMARIZECOLUMNS DAX Function:

  • Use SUMMARIZECOLUMNS for larger datasets or complex scenarios to improve performance.

  • Ensure grouping columns are properly indexed to minimize query execution time.

Related Functions You Might Need

FunctionDescription
SUMMARIZESimilar to SUMMARIZECOLUMNS but less optimized and flexible.
GROUPBYGroups data but requires explicit aggregation for each column.
CALCULATEModifies context for calculations in aggregations.
ADDCOLUMNSAdds calculated columns to an existing table.

Want to Learn More?
For more information, check out the official Microsoft documentation for SUMMARIZECOLUMNS You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What is the SUMMARIZECOLUMNS function in Power BI?

The SUMMARIZECOLUMNS function creates summarized tables by grouping data and applying calculations, optimized for performance.

2. How is SUMMARIZECOLUMNS different from SUMMARIZE?

SUMMARIZECOLUMNS is more efficient and better suited for large datasets, with explicit handling of filters.

3. Can SUMMARIZECOLUMNS handle multiple filters?

Yes, it supports multiple filters as additional parameters.

4. Is SUMMARIZECOLUMNS suitable for measures?

No, it is primarily used in calculated tables rather than measures.

5. Can I use SUMMARIZECOLUMNS with slicers or filters?

Yes, it dynamically respects slicer and filter contexts in reports.

Previous article
Dax Function: SUMMARIZE
Next article
Dax Function: Table Constructor