You find a DAX pattern in an old blog post. It uses SUMMARIZE with a few extension columns, and you paste it into your model. The numbers look plausible, but a few rows are odd and the query feels heavier than it should. Then you open Performance Analyzer on a simple matrix and see Power BI itself using a different function, SUMMARIZECOLUMNS, for the same job.
Which one is right? Why does Power BI favor the second? And why do blank rows vanish from one result but not the other?
This guide compares SUMMARIZE vs SUMMARIZECOLUMNS in plain language. You will see what each function does, how the syntax differs, where each one is a good fit, and where it bites. There are 20 worked examples, a pitfall catalog, a best-practice checklist, and a decision guide.
The sample data model used in this article
Every example uses the same five-table model.
- Customer (dimension): CustomerKey, CustomerID, CustomerName, Gender, BirthDate, Segment, City, State, Country, Region, JoinDate.
- Product (dimension): ProductKey, ProductID, ProductName, Category, Subcategory, Brand, Color, Size, UnitCost, ListPrice, LaunchDate.
- Date (dimension, marked as the date table on [Date]): Date, DateKey, Year, QuarterNumber, Quarter, MonthNumber, MonthName, MonthYear, MonthYearSort, WeekNumber, DayOfWeekNumber, DayName, IsWeekend, IsHoliday, FiscalYear, FiscalQuarter.
- Sales (fact, one row per order): SalesOrderID, OrderNumber, CustomerKey, OrderDate, ShipDate, SalesChannel, PaymentMethod, Freight, TaxAmount.
- Sales Order Details (fact, one row per product per order): SalesOrderLineID, SalesOrderID, ProductKey, Quantity, UnitPrice, DiscountPct, SalesAmount, TotalCost.
All relationships are one-to-many with single-direction filtering. Customer and Date (via OrderDate, active) filter Sales, which filters Sales Order Details. Date also relates to Sales[ShipDate] through an inactive relationship. Product filters Sales Order Details only. That last point matters later, because header-level amounts such as Freight are not filtered by Product.
These base measures are defined once and reused throughout:
Total Sales = SUM ( 'Sales Order Details'[SalesAmount] )
Total Cost = SUM ( 'Sales Order Details'[TotalCost] )
Total Profit = [Total Sales] - [Total Cost]
Profit Margin % = DIVIDE ( [Total Profit], [Total Sales] )
Total Freight = SUM ( Sales[Freight] )
Order Count = DISTINCTCOUNT ( Sales[SalesOrderID] )Concepts: what "summarizing" means in DAX
Grouping, like GROUP BY in SQL
In SQL, GROUP BY Category collapses many rows into one row per category. DAX summarizing does the same. You give it columns to group by, and it returns a table with one row per distinct combination. Optionally you add calculated columns, such as a sales total for each group.
Think of sorting a pile of receipts into folders by category. The folder labels are your group-by columns. A number written on each folder's cover, like the total spent, is a named expression.
SUMMARIZE in one sentence
SUMMARIZE starts from a base table. It groups by columns from that table, or from tables related to it on the "one" side. It can also add extension columns. The base table is the anchor, so the result contains only combinations that actually exist in it.
SUMMARIZECOLUMNS in one sentence
SUMMARIZECOLUMNS has no base table. You list the group-by columns first, from any tables in the model. Then you add optional filter tables, then named expressions. The model's relationships decide how the expressions are filtered by those columns.
Table functions versus measures
A measure returns a single value. Both functions here return a table. That makes them useful in three places:
- Calculated tables, where the result is stored in the model at refresh.
- DAX queries, using
EVALUATE. - Table arguments inside other functions, such as
CALCULATE,COUNTROWS, orTOPN.
Why Power BI visuals use SUMMARIZECOLUMNS
A table or matrix visual is a grouping problem: group by the fields you dragged in, then evaluate the measures. In general terms, Power BI builds a DAX query for each visual, and that query is typically based on SUMMARIZECOLUMNS. Slicer and filter selections usually appear in the query as filter tables.
You can inspect these queries in two ways. In Performance Analyzer, you can look at the query behind a visual and copy it. In the DAX query view, you can paste the query, run it, and edit it. Menu labels change between releases, so look for the query or copy-query options in those tools.
Syntax of each function
SUMMARIZE
SUMMARIZE (
<Table>,
<GroupByColumn> [, <GroupByColumn> ... ]
[, <Name>, <Expression> ... ]
)- Table: the base table. Any table expression works, but it is usually a table such as 'Sales Order Details' or Sales.
- GroupByColumn: a column from the base table, or from a related table reachable through many-to-one relationships.
- Name / Expression pairs: optional extension columns. The name is a text label and the expression is evaluated for each group.
Subtotal helpers are also available:
- ROLLUP wraps group-by columns to request subtotal rows.
- ROLLUPGROUP treats several columns as one unit within a rollup.
- ISSUBTOTAL is used in a name/expression pair and returns TRUE on subtotal rows for a given column.
SUMMARIZECOLUMNS
SUMMARIZECOLUMNS (
<GroupByColumn> [, <GroupByColumn> ... ]
[, <FilterTable> ... ]
[, <Name>, <Expression> ... ]
)- GroupByColumn: columns from any tables in the model.
- FilterTable: a table expression whose columns act as filters on the model, for example a
TREATASresult orFILTERoutput. - Name / Expression pairs: the calculated columns, usually measures.
- ROLLUPADDISSUBTOTAL: wraps a group-by column to add subtotal rows and a Boolean column that flags them.
The function has additional modifiers. I leave them out here because the details are best confirmed in the current documentation.
Key behavioral differences
1. Where group-by columns come from
With SUMMARIZE, the group-by columns must come from the base table or from tables on the "one" side of a relationship chain starting at it. Grouping 'Sales Order Details' by Product[Category] works because each line has exactly one product. With SUMMARIZECOLUMNS, you simply name the columns, and relationships do the rest.
2. Which combinations are returned
SUMMARIZE returns only combinations that exist in the base table. If a category has no rows in 'Sales Order Details', it never appears. If you group a dimension table instead, such as SUMMARIZE ( Product, Product[Category] ), you get every category in the dimension whether or not it sold anything. The base table defines the universe.
3. Rows where every expression is blank
SUMMARIZECOLUMNS removes rows where all named expressions are blank. A category with no sales in the selected period disappears from the result. This is what you want in most visuals, because nobody wants a table of empty rows. It is a nasty surprise if you expected a complete list of categories with blanks or zeros.
The word "all" matters. If you add three measures and only one has a value, the row stays. It is dropped only when every named expression is blank.
4. How filtering works
SUMMARIZE evaluates its base table in the current filter context. Filters come from the surrounding context, or from CALCULATETABLE wrapped around it. SUMMARIZECOLUMNS accepts explicit filter table arguments that restrict the result directly. In a plain query with no outer context, that makes filtering visible right in the formula.
5. How extension columns are evaluated
Extension columns in SUMMARIZE are evaluated per group, and the details of that evaluation can produce surprising results in some situations. Common guidance is to keep SUMMARIZE for grouping only and add calculations with ADDCOLUMNS:
ADDCOLUMNS (
SUMMARIZE ( 'Sales Order Details', Product[Category] ),
"Total Sales", [Total Sales]
)In this form, ADDCOLUMNS creates a row context for each group row, and the measure reference triggers context transition. The behavior is easy to reason about, and that is the main appeal.
6. Context transition and where each function can be used
Both functions work in calculated tables and DAX queries. SUMMARIZE is commonly used inside measures, for example as a table argument to COUNTROWS or CALCULATE. SUMMARIZECOLUMNS has historically had restrictions when used inside measures or where a row or filter context already exists. Support may have changed in newer releases, and the rules can depend on the engine version. I won't state exact current rules, so check the current documentation for them.
Comparison table
| Aspect | SUMMARIZE | SUMMARIZECOLUMNS |
|---|---|---|
| Purpose | Group a base table, keeping its row logic | Group by columns from any tables, report-style |
| Input | A base table is required | No base table; starts with group-by columns |
| Group-by sources | Base table, plus related "one-side" tables | Any tables, connected through model relationships |
| Extension columns | Supported, but adding them directly is discouraged | Named expressions are the standard way to add values |
| Filter arguments | No dedicated filter arguments; use CALCULATETABLE or the existing context | Explicit filter table arguments |
| Empty-row behavior | Returns combinations that exist in the base table; blank extension values are kept | Drops rows where all named expressions are blank |
| Subtotal support | ROLLUP, ROLLUPGROUP, ISSUBTOTAL | ROLLUPADDISSUBTOTAL |
| Use inside measures | Commonly used as a table argument | Historically restricted; check current documentation |
| Typical use case | Distinct combinations, counting groups, table arguments | DAX queries, report-style tables, calculated tables |
| Main limitation | Extension columns can surprise you | Blank-row removal and context restrictions |
Pros, cons, and limitations
Performance depends on the model and the query, so I make no speed claims here.
SUMMARIZE
Pros
- Works from a base table you choose, so the row universe is explicit.
- Keeps empty groups when you summarize a dimension table.
- Commonly used as a table argument inside measures.
- Compact for distinct-combination logic.
Cons and limitations
- Extension columns can produce surprising results, so they are best avoided.
- No explicit filter table arguments.
- Group-by columns are limited to the base table and its related "one-side" tables.
- Calculated values require a second function,
ADDCOLUMNS.
SUMMARIZECOLUMNS
Pros
- Group-by columns can come from any tables.
- Filter tables are explicit, visible, and testable.
- Named expressions are the built-in mechanism for calculations.
- Mirrors the query shape Power BI uses for visuals, so it is a natural fit for DAX query work.
Cons and limitations
- Removes rows where every named expression is blank.
- Has had restrictions in measures and in row or filter context.
- The result is static when used in a calculated table.
Which one to use: a decision guide
Start here
|
+-- Is this a DAX query or a report-style table?
| YES --> SUMMARIZECOLUMNS
|
+-- Do I start from a specific base table and need its rows?
| YES --> SUMMARIZE (add calculations with ADDCOLUMNS)
|
+-- Do I need every row of a dimension kept, even with no data?
| YES --> ADDCOLUMNS ( SUMMARIZE ( dimension table, ... ), ... )
|
+-- Am I inside a measure?
| YES --> SUMMARIZE or VALUES-based patterns; check the docs
| before using SUMMARIZECOLUMNS
|
+-- Does the result need to respond to slicers?
YES --> Use a measure, not a calculated table
When to use SUMMARIZE
- You want distinct combinations from a specific base table.
- You need a table argument inside a measure, such as
COUNTROWS ( SUMMARIZE ( ... ) ). - You want to keep every row of a dimension and then add values with
ADDCOLUMNS.
When to use SUMMARIZECOLUMNS
- You are writing a DAX query in the DAX query view.
- You want group-by columns from several tables with explicit filters.
- You want blank-only rows dropped automatically.
When to use something else
ADDCOLUMNSwithVALUESorSUMMARIZEover a dimension keeps all dimension rows.GROUPBYgroups an existing table expression and is typically paired withCURRENTGROUPinside iterator functions. Check the documentation before relying on it for new patterns.
Rule of thumb: SUMMARIZECOLUMNS for queries and reports, SUMMARIZE for grouping a base table, and ADDCOLUMNS for any calculation added to a SUMMARIZE.
20 worked examples
Beginner: SUMMARIZE (1 to 5)
Example 01: Product categories that have sales
Type: DAX query. Scenario: a merchandiser wants the list of categories that have actually sold.
EVALUATE
SUMMARIZE ( 'Sales Order Details', Product[Category] )This returns one row per distinct category found in order lines. Categories that never sold do not appear, because the base table is 'Sales Order Details', not Product. Choose SUMMARIZE when the base table defines which combinations count. A blank category row would appear only if some lines had no matching product.
Example 02: Countries that have orders
Type: DAX query. Scenario: a regional manager wants the countries where customers have ordered.
EVALUATE
SUMMARIZE ( Sales, Customer[Country] )Customer[Country] is not in Sales, but it works. Customer is on the "one" side of the Customer to Sales relationship, so each order maps to exactly one customer and therefore one country. SUMMARIZE follows that relationship. Countries with customers but no orders are not returned.
Example 03: Two group-by columns
Type: DAX query. Scenario: a category manager wants every Category and Subcategory pair that sold.
EVALUATE
SUMMARIZE (
'Sales Order Details',
Product[Category],
Product[Subcategory]
)The result has one row per distinct pair present in order lines. Each added column makes the groups finer, so the row count can only stay the same or grow. This is the simplest way to build a small lookup of valid combinations.
Example 04: Extension column inside SUMMARIZE
Type: DAX query. Scenario: a quick sales-by-category view, written the way older posts did it.
EVALUATE
SUMMARIZE (
'Sales Order Details',
Product[Category],
"Total Sales", [Total Sales]
)This returns each category with its sales, and in simple cases the numbers look right. The pattern is discouraged because extension columns inside SUMMARIZE can behave in surprising ways in some scenarios. It is easy to be sure of the result only after testing. The next example shows the safer version.
Example 05: The recommended pattern with ADDCOLUMNS
Type: DAX query. Scenario: the same category sales view, written defensively.
EVALUATE
ADDCOLUMNS (
SUMMARIZE ( 'Sales Order Details', Product[Category] ),
"Total Sales", [Total Sales]
)SUMMARIZE only decides the groups. ADDCOLUMNS then walks each group row and evaluates the measure, with context transition turning the row's category into a filter. Each function does one job, which makes the result easier to predict and review.
Intermediate: SUMMARIZE (6 to 8)
Example 06: Subtotals with ROLLUP and ISSUBTOTAL
Type: DAX query. Scenario: a finance lead wants category sales with a grand total row.
EVALUATE
SUMMARIZE (
'Sales Order Details',
ROLLUP ( Product[Category] ),
"Total Sales", [Total Sales],
"Is Grand Total", ISSUBTOTAL ( Product[Category] )
)
ORDER BY [Is Grand Total], Product[Category]You get one row per category, plus one extra row where Category is blank and "Is Grand Total" is TRUE. That extra row carries the total across all categories. This is one of the few places where an extension column belongs inside SUMMARIZE, because ISSUBTOTAL only works there. Test it in the DAX query view with your own data.
Example 07: Counting customers who bought in a period
Type: DAX query. Scenario: marketing wants the number of active customers in 2025.
EVALUATE
ROW (
"Customers via SUMMARIZE",
CALCULATE (
COUNTROWS ( SUMMARIZE ( Sales, Sales[CustomerKey] ) ),
'Date'[Year] = 2025
),
"Customers via DISTINCTCOUNT",
CALCULATE (
DISTINCTCOUNT ( Sales[CustomerKey] ),
'Date'[Year] = 2025
)
)Both columns should return the same number. SUMMARIZE builds a table of distinct customer keys and COUNTROWS counts it, while DISTINCTCOUNT does it in one step. Prefer DISTINCTCOUNT for a simple count. The SUMMARIZE form becomes useful when you group by several columns before counting.
Example 08: SUMMARIZE as a filter argument in CALCULATE
Type: fragment (a new measure, 'Sales to 2025 Buyers'). Scenario: analysts want sales from customers who placed an order in 2025.
Sales to 2025 Buyers =
CALCULATE (
[Total Sales],
CALCULATETABLE (
SUMMARIZE ( Sales, Sales[CustomerKey] ),
'Date'[Year] = 2025
)
)The inner table lists customer keys with an order in 2025. Used as a filter argument, it restricts Sales to those customers. Any year selected in the visual still applies to the result, so by year you see how much those buyers spent in each period.
Beginner: SUMMARIZECOLUMNS (9 to 13)
Example 09: A basic query with ORDER BY
Type: DAX query. Scenario: a sales lead wants sales by category, largest first.
EVALUATE
SUMMARIZECOLUMNS (
Product[Category],
"Total Sales", [Total Sales]
)
ORDER BY [Total Sales] DESCThis is the canonical shape. Group-by column first, named expression second. Categories with blank sales are dropped. ORDER BY sits outside the function, and it is what guarantees the sort.
Example 10: Group-by columns from different tables
Type: DAX query. Scenario: an executive wants sales by category for each year.
EVALUATE
SUMMARIZECOLUMNS (
Product[Category],
'Date'[Year],
"Total Sales", [Total Sales]
)
ORDER BY Product[Category], 'Date'[Year]Product and Date are separate dimensions with no direct relationship. They meet at the fact tables, and the measure sums 'Sales Order Details'. SUMMARIZECOLUMNS needs no base table here, which is its main convenience. Only category and year pairs with sales are returned.
Example 11: A filter table with TREATAS
Type: DAX query. Scenario: the same category view, limited to 2025.
EVALUATE
SUMMARIZECOLUMNS (
Product[Category],
TREATAS ( { 2025 }, 'Date'[Year] ),
"Total Sales", [Total Sales]
)
ORDER BY [Total Sales] DESCTREATAS turns the value 2025 into a filter on 'Date'[Year]. That filter flows Date to Sales to Sales Order Details, so the measure only sums 2025 lines. It does not add a Year column to the output, and it does not filter Product directly. Categories with no 2025 sales disappear.
Example 12: Multiple named expressions
Type: DAX query. Scenario: a controller wants sales, profit, and margin by brand.
EVALUATE
SUMMARIZECOLUMNS (
Product[Brand],
"Total Sales", [Total Sales],
"Total Profit", [Total Profit],
"Profit Margin %", [Profit Margin %]
)
ORDER BY [Total Profit] DESCEach name/expression pair becomes a column, and measures are reused rather than rewritten. A brand row is dropped only if all three expressions are blank. The margin comes back as a decimal, so format it as a percentage where it is displayed.
Example 13: A calculated table
Type: calculated table, new table 'Sales by Category and Year'. Scenario: a stored summary for a lookup or snapshot.
'Sales by Category and Year' =
SUMMARIZECOLUMNS (
Product[Category],
'Date'[Year],
"Sales Amount", [Total Sales]
)The table is computed when the model refreshes and then stored. It does not respond to slicers afterward. I named the column "Sales Amount" so it never clashes with the [Total Sales] measure. Use a calculated table when you need a physical table, not just a result.
Intermediate: SUMMARIZECOLUMNS (14 to 17)
Example 14: Subtotals with ROLLUPADDISSUBTOTAL
Type: DAX query. Scenario: a category report that includes an overall total row.
EVALUATE
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL ( Product[Category], "Is Grand Total" ),
"Total Sales", [Total Sales]
)
ORDER BY [Is Grand Total], [Total Sales] DESCThe result has one row per category with "Is Grand Total" set to FALSE. It also has one extra row where Category is blank and the flag is TRUE, holding the total across all categories. The flag column is how you tell a real blank category from a subtotal row. Sorting by it pushes the total row to the bottom.
Example 15: The blank-row behavior, side by side
Type: two DAX queries. Scenario: a planner wants to see which categories had no sales in 2025.
-- Query A: drops categories with no 2025 sales
EVALUATE
SUMMARIZECOLUMNS (
Product[Category],
TREATAS ( { 2025 }, 'Date'[Year] ),
"Total Sales", [Total Sales]
)
-- Query B: keeps every category in the Product table
EVALUATE
ADDCOLUMNS (
SUMMARIZE ( Product, Product[Category] ),
"Total Sales", CALCULATE ( [Total Sales], 'Date'[Year] = 2025 )
)Query A returns only categories where the measure is non-blank. Query B starts from Product, so every category appears, and categories without 2025 sales show a blank value. Readers see fewer rows from A and a complete list with gaps from B. Use A for reports and B when "what is missing" is the question.
Example 16: Three group-by tables
Type: DAX query. Scenario: a regional review of category sales by country and year.
EVALUATE
SUMMARIZECOLUMNS (
Product[Category],
Customer[Country],
'Date'[Year],
"Total Sales", [Total Sales]
)
ORDER BY Customer[Country], 'Date'[Year], Product[Category]Customer filters Sales, Date filters Sales, and Product filters Sales Order Details directly. Sales in turn filters Sales Order Details, so all three sets of filters reach the table the measure sums. Only combinations with sales are returned.
Example 17: Top 5 customers with TOPN
Type: DAX query. Scenario: the sales director wants the five biggest customers.
EVALUATE
TOPN (
5,
SUMMARIZECOLUMNS (
Customer[CustomerID],
Customer[CustomerName],
"Customer Sales", [Total Sales]
),
[Customer Sales], DESC
)
ORDER BY [Customer Sales] DESCThe order of operations goes inside out. First SUMMARIZECOLUMNS builds one row per customer with sales. Then TOPN keeps the five highest. If there are ties at the cut-off, TOPN can return more than five rows. Finally ORDER BY sorts the output. I used a distinct column name to avoid any ambiguity with the measure.
Advanced: comparing and deciding (18 to 20)
Example 18: The same result both ways, with a reconciliation
Type: DAX query. Scenario: an analyst proves two builds agree before choosing one.
DEFINE
VAR ByColumns =
SUMMARIZECOLUMNS (
Product[Category],
'Date'[Year],
"Sales Amount", [Total Sales]
)
VAR ByAddColumns =
ADDCOLUMNS (
SUMMARIZE ( 'Sales Order Details', Product[Category], 'Date'[Year] ),
"Sales Amount", [Total Sales]
)
EVALUATE
ROW (
"Rows SUMMARIZECOLUMNS", COUNTROWS ( ByColumns ),
"Rows ADDCOLUMNS", COUNTROWS ( ByAddColumns ),
"Sales SUMMARIZECOLUMNS", SUMX ( ByColumns, [Sales Amount] ),
"Sales ADDCOLUMNS", SUMX ( ByAddColumns, [Sales Amount] ),
"Total Sales", [Total Sales]
)All three sales figures should match, and so should the two row counts. A difference deserves investigation. For example, line items with a blank SalesAmount would leave a combination that exists in the base table but evaluates to blank. SUMMARIZE would keep that combination, while SUMMARIZECOLUMNS would drop it. A zero is not a blank, so zero-value groups survive in both.
Example 19: Reading a visual's generated query
Type: explanation. Scenario: you copied a query from Performance Analyzer and want to understand it.
I won't reproduce exact generated text, because it varies by visual type and release. In general, the query has a recognizable shape:
- Variables, typically declared first, that hold filter tables built from slicer and filter selections.
- A core
SUMMARIZECOLUMNScall that lists the visual's fields as group-by columns, applies those filters, and adds each measure as a named expression. - For tables with totals, subtotal helpers such as
ROLLUPADDISSUBTOTALare commonly involved. - A
TOPNwrapper that limits the row count, and anORDER BYthat mirrors the visual's sort.
Paste the query into the DAX query view, run it, and change one piece at a time. That is the fastest way to learn what each part does.
Example 20: A decision checklist
Type: checklist. Scenario: you are about to pick a function and want a quick check.
- Do I need a specific base table? Yes points to
SUMMARIZE. - Do I need rows with no data kept? Yes points to
ADDCOLUMNSover aSUMMARIZEof the dimension. - Am I inside a measure? Be careful with
SUMMARIZECOLUMNSand check the current documentation. - Do I need explicit filters?
SUMMARIZECOLUMNSaccepts filter tables directly. - Is this a one-off query or a persistent calculated table? Build the query first, then decide whether it needs to be stored at all.
Two or three "yes" answers on one side usually settle it.
Pitfalls and how to avoid them
Pitfall 1: Extension columns directly inside SUMMARIZE
What happens: results can look right in simple cases and surprise you in others.
Why it happens: extension columns are evaluated per group inside the function, in ways that are hard to reason about.
How to fix it: group in SUMMARIZE and calculate in ADDCOLUMNS.
-- Risky
SUMMARIZE ( 'Sales Order Details', Product[Category], "Total Sales", [Total Sales] )
-- Safer
ADDCOLUMNS (
SUMMARIZE ( 'Sales Order Details', Product[Category] ),
"Total Sales", [Total Sales]
)Pitfall 2: Expecting SUMMARIZECOLUMNS to keep rows with no data
What happens: groups with no sales are missing.
Why it happens: rows where every named expression is blank are removed.
How to fix it: start from the dimension with SUMMARIZE and add values with ADDCOLUMNS, or accept the removal.
EVALUATE
ROW (
"Rows SUMMARIZECOLUMNS",
COUNTROWS (
SUMMARIZECOLUMNS (
Product[Brand],
TREATAS ( { 2025 }, 'Date'[Year] ),
"Total Sales", [Total Sales]
)
),
"Rows ADDCOLUMNS",
COUNTROWS (
ADDCOLUMNS (
SUMMARIZE ( Product, Product[Brand] ),
"Total Sales", CALCULATE ( [Total Sales], 'Date'[Year] = 2025 )
)
)
)If Product held 12 brands and only 9 sold in 2025 (a hypothetical case), the first count would be 9 and the second 12.
Pitfall 3: SUMMARIZECOLUMNS inside a measure or existing context
What happens: an error, or a result you did not intend.
Why it happens: the function has historically had restrictions when a row or filter context already exists. Support may have changed in newer releases, and the rules can depend on the engine version.
How to fix it: use SUMMARIZE, ADDCOLUMNS with VALUES, or an iterator in measures, and check the current documentation for the exact rules. Test your specific case in the DAX query view.
Pitfall 4: High-cardinality group-by columns
What happens: a huge result table, slower refresh, and a larger model if stored.
Why it happens: one row is produced per distinct combination, and columns like Sales[OrderNumber] are nearly unique.
How to fix it: group only by what the reader needs.
-- Risky: one row per order
SUMMARIZECOLUMNS ( Sales[OrderNumber], "Total Sales", [Total Sales] )
-- Safer: a handful of rows per month
SUMMARIZECOLUMNS ( 'Date'[MonthYear], "Total Sales", [Total Sales] )Customer[CustomerName] needs care too, since names may not be unique. Group by Customer[CustomerID] alongside it.
Pitfall 5: Forgetting that calculated tables are fixed at refresh
What happens: slicers appear to do nothing to a visual built on the table.
Why it happens: a calculated table is evaluated when the model refreshes and then stored as static data.
How to fix it: if the result must respond to slicers, use a measure and let the visual do the grouping.
Pitfall 6: A calculated table that duplicates a measure
What happens: extra model size and a second place to maintain logic.
Why it happens: it is tempting to materialize "sales by category" when [Total Sales] with a Category field gives the same numbers at query time.
How to fix it: reserve calculated tables for needs a measure cannot meet, such as a stored snapshot or a table you need to relate to others.
Pitfall 7: Slicing a header-level amount by a Product column
What happens: the same freight value repeats on every category row.
Why it happens: Product filters 'Sales Order Details', but not Sales, where Freight lives.
-- Misleading: Total Freight does not respond to Product[Category]
EVALUATE
SUMMARIZECOLUMNS (
Product[Category],
"Total Freight", [Total Freight]
)How to fix it: slice freight by Sales-level or dimension columns that filter Sales, such as Customer, Date, or Sales[SalesChannel]. If you truly need freight by product, define an explicit allocation rule first. That rule would be a new column or measure you must introduce and document. Also, summing the repeated values would overstate freight.
Pitfall 8: Unclear or clashing result column names
What happens: confusing columns, possible errors, or ambiguous references.
Why it happens: a result column named like an existing column or measure, such as "Total Sales" or "Category", is easy to mix up. In a calculated table, such a name makes unqualified [Total Sales] references unclear. I would not rely on how duplicate names are resolved, so test it in the DAX query view.
How to fix it: use distinct, descriptive names, such as "Sales Amount" or "Customer Sales".
Pitfall 9: Trusting a result without reconciling it
What happens: wrong numbers reach a report unnoticed.
Why it happens: a grouped total that differs from the simple [Total Sales] usually points to a filter, blank-row, or relationship issue.
How to fix it: compare the sum of the grouped column to the measure, as in Example 18, and compare row counts between approaches.
Pitfall 10: Assuming result order
What happens: rows come back in an order that changes or looks random.
Why it happens: table functions do not guarantee a sorted result.
How to fix it: use ORDER BY in queries, or sort explicitly in your logic.
Best practices checklist
- Use SUMMARIZECOLUMNS for queries and report-style grouping, and SUMMARIZE with ADDCOLUMNS when you start from a base table. Each has a natural home, and sticking to it reduces surprises.
- Add calculations with ADDCOLUMNS around SUMMARIZE. It keeps the evaluation easy to reason about.
- Write and test the query in the DAX query view first. You see row counts and values before committing anything to the model.
- Name every result column clearly and consistently. Distinct names avoid clashes with measures and columns.
- Reuse base measures instead of repeating logic. One definition means one place to fix.
- Use variables and consistent formatting. Readable DAX is easier to review and debug.
- Keep group-by columns to what the reader needs. Fewer, lower-cardinality columns keep results small.
- Reconcile every grouped result against a simple measure total, and check row counts. It catches filter, blank-row, and relationship problems early.
- Prefer a measure over a calculated table when the result must respond to slicers. Calculated tables are static between refreshes.
- Document persistent calculated tables. Note the purpose, the source, and the refresh behavior for whoever maintains the model next.
Quick reference
| Situation | Recommended approach |
|---|---|
| Report-style grouping in a DAX query | SUMMARIZECOLUMNS |
| Distinct combinations from a base table | SUMMARIZE |
| Add values to grouped rows | ADDCOLUMNS ( SUMMARIZE ( ... ), ... ) |
| Keep all dimension rows, even with no data | ADDCOLUMNS over SUMMARIZE of the dimension |
| Count distinct customers who bought | DISTINCTCOUNT ( Sales[CustomerKey] ) |
| Result must respond to slicers | A measure, not a calculated table |
| Subtotals in a query | ROLLUPADDISSUBTOTAL, or ROLLUP with ISSUBTOTAL |
| Grouping inside a measure | SUMMARIZE or VALUES patterns; check the docs for SUMMARIZECOLUMNS |
Frequently asked questions
Is SUMMARIZE deprecated?
I am not aware of any announcement saying so, and I have no basis to claim it. It remains a documented function. If you want to be sure for your version, check the current documentation.
Why does SUMMARIZECOLUMNS return fewer rows than I expected?
It drops rows where all named expressions are blank, as in Examples 11 and 15. Filters can also remove groups. To see the groups with no data, start from the dimension with SUMMARIZE and add values with ADDCOLUMNS.
Can I use SUMMARIZECOLUMNS in a measure?
Historically it has had restrictions in measures and wherever a row or filter context exists. Support may have changed in newer releases, so check the current documentation and test your exact scenario in the DAX query view before relying on it.
Is SUMMARIZECOLUMNS always faster?
No. Performance depends on the model and the query, so measure both options on your own data rather than assuming. Choose by behavior and fit for the task.
Conclusion: key takeaways
- SUMMARIZE groups a base table. Add calculations with
ADDCOLUMNS, not inside it. - SUMMARIZECOLUMNS groups by columns from any tables, takes explicit filters, and drops rows where every named expression is blank.
- Check the current documentation before using
SUMMARIZECOLUMNSin a measure. - Reconcile every grouped result, and test in the DAX query view first.
- Rule of thumb: SUMMARIZECOLUMNS for queries and reports, SUMMARIZE for a base table, and ADDCOLUMNS for any calculation.
Related reading: see my earlier articles on CALCULATE, calculated tables and columns, and explicit measures.
Want more practical Power BI, DAX, and SSAS Tabular guides? Visit exceltobi.com for new tutorials, patterns, and model-design tips.