DAX SUMMARIZECOLUMNS Function

The SUMMARIZECOLUMNS function in DAX is a powerful tool used in Power BI to group and summarize the data. It is often preferred over the older SUMMARIZE function because it’s faster, simpler to write, and works better when building reports. With SUMMARIZECOLUMNS, you can easily create summary tables, apply filters, and calculate values like total sales or quantity. In this blog, you will explore how to use the SUMMARIZECOLUMNS function in Power BI to load data, create summaries, and improve your DAX reports.

Table of Contents:

• Why Use SUMMARIZECOLUMNS in Power BI?
• Advantages of Using SUMMARIZECOLUMNS in Power BI
• Sample Dataset
• Steps to Use SUMMARIZECOLUMNS in Power BI
  • Step 1: Load your dataset
  • Step 2: Define your query
  • Step 3: Output
• Comparison: SUMMARIZECOLUMNS vs SUMMARIZE
• Best Practices
• Limitations of SUMMARIZECOLUMNS
• Conclusion
• SUMMARIZECOLUMNS Function (DAX) – FAQs

Why Use SUMMARIZECOLUMNS in Power BI?

SUMMARIZECOLUMNS is a function in DAX used in Power BI that improves query performance and code writing for you. Unlike SUMMARIZE, it identifies the correct data sources by removing unnecessary complexity. It also generates better queries, which ultimately help with large data sources. By using SUMMARIZECOLUMNS, you reduce the execution time of the query and make better reports.

Advantages of Using SUMMARIZECOLUMNS in Power BI

• Enhanced Performance: This means better performance with less processing time and better query plans.
• Simplified Syntax: There is better syntax since you do not need to reference the base table.
• More Flexible Filtering: Multiple filters can be applied directly to the table.
• Timely Reporting: Reports are cleaner and easier to combine with visuals and KPIs. 
• Improved Context Management: Avoids unnecessary row context issues.

Sample Dataset

Tables that we use to perform calculations.

1. Sales Table

2. Date Table

3. Product table

Required Relationships:

To execute SUMMARIZECOLUMNS queries, make sure to establish the correct relationship between tables, as the wrong relationship may lead to incorrect results.

• Date[Year] → Sales[Year](One-to-Many)
• Sales[Product]→Product[Product](Many-to-One)

Steps to Use SUMMARIZECOLUMNS in Power BI

Let’s explore the steps to use SUMMARIZECOLUMNS in Power BI.

Step 1: Load your dataset

1. Sales Table

This is the Sales Table in Power BI.

2. Date Table

This is a Date Table in Power BI.

3. Product Table

This is the Product Table in Power BI.

Step 2: Define your query

Create a summary of sales by year and product colour.

Go to “Model View,” click on “New Table,” and write the formula in the formula bar.

SalesSummary =

SUMMARIZECOLUMNS (

    'Date'[Year],

    'Product'[Color],

    "Total Sales", SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )

)

Explanation:

• SalesSummary is used to create a new table and summarize data based on the logic
• SUMMARIZECOLUMNS() is a function used to group and summarise data by specific columns.

Step 3: Output

Explanation: Here, the DAX function is used to summarize the three tables into one table.

Comparison: SUMMARIZECOLUMNS vs SUMMARIZE

Best Practices

• Establish Relationships: Make sure that the correct relationships are set between tables to avoid incorrect output.
• Minimise Filters: Apply only necessary filters to avoid unnecessary data processing. Use additional filters only when needed.
• Use Aggregation: Perform calculations using SUMX instead of using nested queries, which make queries difficult to debug.
• Test your formula: Test your DAX formula on sample data before applying it to actual data.
• Name Your Table: When working with multiple tables in Power BI, try to name all important tables with meaningful names to avoid any confusion.

Limitations of SUMMARIZECOLUMNS

• No Row Context: SUMMARIZECOLUMNS operates in filter context and does not support row context by default, making row-wise operations like calculated columns behave differently.
• Limited Debugging Support: Debugging SUMMARIZECOLUMNS is hard, as it is a complex expression.
• Not for Complex Calculations: SUMMARIZECOLUMNS is used for aggregation but not for complex calculations.

Conclusion

SUMMARIZECOLUMNS is the best choice when you are building DAX queries, as it improves performance, is very easy to maintain, and is a powerful function that helps in aggregation tasks. Building reports in Power BI can be very efficient only if you are using SUMMARIZECOLUMNS, as it has a clear syntax and great compatibility with other DAX functions, and can be the best option in Power BI. In this blog, you have learned about SUMMARIZECOLUMNS and its usage.

To learn more about Power BI and its functions, check out this Power BI Course and also explore Power BI Interview Questions prepared by industry experts.

SUMMARIZECOLUMNS Function (DAX) – FAQs