DAX CALCULATE Function: Complete Tutorial with 50 Examples (Part - III)

---

This is where CALCULATE starts to feel like a different language. You'll change which relationship carries a filter, pass tables built from other tables, combine iterators with context transition, and write measures that rank, accumulate, and segment dynamically.

Two examples need a small addition to the base model, and each is flagged where it appears:

• Example 27 assumes an extra column, Sales[ShipDate], with an inactive relationship to 'Date'[Date].
• Example 36 assumes a small disconnected table named 'Spend Band', defined in that example.

Example 27: Sales by Ship Date with USERELATIONSHIP

Scenario: By default your Date table filters Sales by OrderDate. Logistics wants to see sales by the date the order shipped. (Assumption: Sales[ShipDate] exists and has an inactive relationship to 'Date'[Date].)

Sales by Ship Date =
CALCULATE (
    [Total Sales],
    USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] )
)

How it evaluates: A model can have only one active relationship between two tables, so the ShipDate relationship sits inactive until you ask for it. USERELATIONSHIP activates it for the duration of this CALCULATE and deactivates the OrderDate relationship between the same two tables. Placed in a monthly visual, the measure groups sales by the month they shipped instead of the month they were ordered.

Example 28: Customers Who Bought a Product with CROSSFILTER

Scenario: When a user selects a product category, you want to know how many customers bought from it. Normally a filter on Product reaches Sales but does not continue on to Customer.

Customers Who Bought =
CALCULATE (
    COUNTROWS ( Customer ),
    CROSSFILTER ( Sales[CustomerKey], Customer[CustomerKey], BOTH )
)

How it evaluates: CROSSFILTER changes the filter direction of the Sales-to-Customer relationship to bidirectional for this calculation only. A filter on Product now flows into Sales, and from Sales back up to Customer. COUNTROWS then counts only the customers that still have matching sales. This avoids turning on bidirectional filtering for the whole model, which can slow things down and create ambiguity.

Example 29: Applying a Filter to an Unrelated Column with TREATAS

Scenario: Marketing wants sales for a fixed group of North American markets as a single number.

North America Sales =
CALCULATE (
    [Total Sales],
    TREATAS ( { "United States", "Canada" }, Customer[Country] )
)

How it evaluates: TREATAS takes a table of values and applies it as a filter on the column you name, as if those values came from that column. The two countries become a filter on Customer[Country] and flow through the relationship to Sales. The same technique is the standard way to filter a model with a disconnected table, such as a selector table used in a slicer: you pass the selected values to TREATAS and point them at a real column.

Example 30: Average Sales per Customer (Context Transition in an Iterator)

Scenario: A manager wants to know how much the typical customer spends, not how much the average order line brings in.

Average Sales per Customer =
AVERAGEX (
    VALUES ( Customer[CustomerKey] ),
    [Total Sales]
)

How it evaluates: AVERAGEX walks through each customer visible in the current context. For every customer, the reference to [Total Sales] triggers context transition, so the measure returns that one customer's sales. Customers with no sales return blank and are ignored by AVERAGEX, so the result averages only customers who actually bought something in the current context.

---

---

Example 31: Best Month's Sales with MAXX and SUMMARIZE

Scenario: A report should show the sales of the strongest month in whatever period the user has selected.

Best Month Sales =
MAXX (
    SUMMARIZE ( 'Date', 'Date'[Year], 'Date'[MonthNumber] ),
    [Total Sales]
)

How it evaluates: SUMMARIZE builds a table with one row per year-and-month combination present in the current Date context. MAXX iterates those rows, and for each one the [Total Sales] reference transitions the row into filters on Year and MonthNumber. MAXX then returns the highest value. Using both Year and MonthNumber keeps January 2023 and January 2024 apart.

Example 32: Ranking Brands with RANKX

Scenario: A table visual lists brands, and you want to rank them by sales, respecting any slicer selections.

Brand Sales Rank =
IF (
    HASONEVALUE ( Product[Brand] ),
    RANKX (
        ALLSELECTED ( Product[Brand] ),
        [Total Sales],
        ,
        DESC,
        DENSE
    )
)

How it evaluates: ALLSELECTED returns all brands the user has selected, ignoring the row filter that the visual puts on each individual brand. RANKX evaluates [Total Sales] for each of those brands (using context transition) and finds where the current brand's value falls among them. DENSE means ties share a rank and the next rank is not skipped. The HASONEVALUE check returns blank on total rows, where a single rank makes no sense.

Example 33: Sales of the Top 3 Brands with TOPN

Scenario: An executive card should show how much revenue the three biggest brands bring in together.

Top 3 Brands Sales =
CALCULATE (
    [Total Sales],
    TOPN (
        3,
        ALL ( Product[Brand] ),
        [Total Sales]
    )
)

How it evaluates: Filter arguments are evaluated in the original filter context. TOPN ranks every brand by [Total Sales], respecting outside filters such as the selected year, and returns the top three. CALCULATE applies those three brands as a filter on Product[Brand], replacing any existing brand filter, and [Total Sales] adds them up. In a visual that shows one row per brand, every row will display the same top-three total, because the brand filter is replaced. Ties at third place can return more than three brands.

Example 34: Cumulative (Running Total) Sales

Scenario: A line chart should show sales accumulating from the first date in the model up to each point on the axis.

Cumulative Sales =
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

How it evaluates: MAX('Date'[Date]) is evaluated in the original context and returns the last date of the current point on the axis, such as the end of March. ALL('Date') provides every date in the model, and FILTER keeps the dates up to that last date. CALCULATE replaces the Date filter with that growing set, so each point includes everything before it. Because the whole Date table is cleared, the total does not reset at year boundaries. For a year-to-date reset, use DATESYTD as in Example 18.

Example 35: Cumulative Share of Sales (Pareto Analysis)

Scenario: The category team wants to know which brands make up the first 80% of sales, a classic Pareto (80/20) view.

Brand Cumulative Share % =
IF (
    HASONEVALUE ( Product[Brand] ),
    VAR CurrentBrandSales = [Total Sales]
    VAR BrandsAtOrAbove =
        FILTER (
            ALLSELECTED ( Product[Brand] ),
            [Total Sales] >= CurrentBrandSales
        )
    VAR CumulativeSales =
        CALCULATE ( [Total Sales], BrandsAtOrAbove )
    VAR SelectedTotal =
        CALCULATE ( [Total Sales], ALLSELECTED ( Product[Brand] ) )
    RETURN
        DIVIDE ( CumulativeSales, SelectedTotal )
)

How it evaluates: The first variable captures the current brand's sales. FILTER then keeps every selected brand whose sales are equal to or greater than that value, so the set contains this brand and all the bigger ones. CALCULATE totals those brands, and the final line divides by the total of all selected brands. Sorted by sales descending, the measure rises from the top brand toward 100%. Brands with identical sales are counted together.

Example 36: Dynamic Customer Segmentation by Spend

Scenario: You want to group customers into Low, Medium, and High spend bands and count how many fall into each, with the bands defined in a table rather than hard-coded in DAX. (Assumption: you create the disconnected calculated table below. It must have no relationships.)

Spend Band =
DATATABLE (
    "Band", STRING,
    "MinSales", INTEGER,
    "MaxSales", INTEGER,
    {
        { "Low", 0, 1000 },
        { "Medium", 1000, 5000 },
        { "High", 5000, 1000000000 }
    }
)

Customers in Spend Band =
VAR BandMin = MIN ( 'Spend Band'[MinSales] )
VAR BandMax = MAX ( 'Spend Band'[MaxSales] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Customer[CustomerKey] ),
        FILTER (
            VALUES ( Customer[CustomerKey] ),
            VAR CustomerSales = [Total Sales]
            RETURN
                NOT ISBLANK ( CustomerSales )
                    && CustomerSales >= BandMin
                    && CustomerSales < BandMax
        )
    )

How it evaluates: With Band on the rows of a visual, each row filters the disconnected table, so BandMin and BandMax pick up that band's limits. FILTER then iterates the customers, and for each one [Total Sales] is evaluated through context transition and tested against the limits. The ISBLANK check keeps customers with no sales out of the lowest band. Because spend is calculated in the current filter context, the segments shift as the user changes the year or any other filter.

Example 37: Customers Lost Since Last Year

Scenario: The retention team wants to know how many customers bought in the same period last year but have not bought in the current period.

Lost Customers =
VAR CurrentCustomers =
    VALUES ( Sales[CustomerKey] )
VAR PriorYearCustomers =
    CALCULATETABLE (
        VALUES ( Sales[CustomerKey] ),
        SAMEPERIODLASTYEAR ( 'Date'[Date] )
    )
RETURN
    COUNTROWS ( EXCEPT ( PriorYearCustomers, CurrentCustomers ) )

How it evaluates: CurrentCustomers is the list of customers with sales in the current filter context. CALCULATETABLE, the table-returning sibling of CALCULATE, shifts the dates back a year and returns the customers with sales in that earlier period. EXCEPT keeps the prior-year customers that are missing from the current list, and COUNTROWS counts them. Other filters in the context, such as Country or Product, apply to both lists.

Example 38: Context Transition in a Calculated Column

Scenario: The CRM team wants each customer's lifetime sales stored as a column on the Customer table so it can be used in slicers and groupings.

Customer Lifetime Sales =
CALCULATE ( [Total Sales] )

How it evaluates: A calculated column is evaluated once per row, so there is a row context for the current customer but no filter context. CALCULATE triggers context transition, which converts that customer row into filters on the Customer table. The filter flows to Sales, and [Total Sales] returns the customer's sales across all dates. Remember that a calculated column is computed at data refresh and does not react to slicers, so it suits stable attributes, not interactive numbers.

Example 39: Nested CALCULATE: The Inner Filter Wins

Scenario: You need to understand what happens when two CALCULATE calls modify the same column, because the order of nesting changes the answer.

Bikes Sales (Inner Filter Wins) =
CALCULATE (
    CALCULATE (
        [Total Sales],
        Product[Category] = "Bikes"
    ),
    ALL ( Product[Category] )
)

All Categories Sales (Inner Clear Wins) =
CALCULATE (
    CALCULATE (
        [Total Sales],
        ALL ( Product[Category] )
    ),
    Product[Category] = "Bikes"
)

How it evaluates: The outer CALCULATE sets up its filter context first, and the inner CALCULATE then modifies that result. In the first measure, the outer call clears Category and the inner call then filters to Bikes, so the measure returns Bikes. In the second, the outer call filters to Bikes but the inner call clears Category again, so the measure returns all categories. The order of filter arguments inside one CALCULATE does not matter, but nesting does.

Example 40: 7-Day Moving Average

Scenario: Daily sales are noisy. The analytics team wants a trailing seven-day average to show the trend.

Sales 7-Day Moving Average =
AVERAGEX (
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -7, DAY ),
    [Total Sales] + 0
)

How it evaluates: DATESINPERIOD returns the seven days ending on the last date in the current context. AVERAGEX iterates those days, and each [Total Sales] reference is evaluated for a single day through context transition. Adding zero turns blank days into zeros, so days without sales pull the average down instead of being skipped. This measure is intended for a daily axis, because at a monthly grain it would average the seven days ending on each month's last date.

DAX CALCULATE Function: Complete Tutorial with 50 Examples (Part - II)

---

You now know how to add, replace, and remove filters. This section builds on that with five new skills: preserving existing filters, controlling exactly which filters get cleared, filtering with logic that Boolean expressions can't handle, shifting dates, and using variables to keep measures readable.

The time intelligence examples (18–23) assume the Date table has one row per day, covers every date in Sales without gaps, is marked as a date table, and has an active relationship to Sales[OrderDate]. Time intelligence functions depend on that.

Example 13: Respecting the Slicer with KEEPFILTERS

Scenario: In Example 5 you excluded Accessories, but that measure ignores a Category slicer. Now you want the exclusion to work inside whatever the user has selected.

Sales Excluding Accessories (Slicer-Aware) =
CALCULATE (
    [Total Sales],
    KEEPFILTERS ( Product[Category] <> "Accessories" )
)

How it evaluates: Normally a filter on Product[Category] replaces the existing Category filter. KEEPFILTERS changes that: the new filter is intersected with the existing one. If the slicer has Bikes and Accessories selected, the result covers only Bikes. If the slicer selects only Bikes, the result is still Bikes. If nothing is selected, you get everything except Accessories.

Example 14: Share of Category with ALLEXCEPT

Scenario: A matrix shows Category and Subcategory. For each subcategory, the team wants its share of the parent category's sales.

Subcategory Share of Category % =
DIVIDE (
    [Total Sales],
    CALCULATE (
        [Total Sales],
        ALLEXCEPT ( Product, Product[Category] )
    )
)

How it evaluates: ALLEXCEPT removes every filter on the Product table except the one on Category. On a subcategory row, the Subcategory and Brand filters are cleared while Category stays, so the denominator is the whole category's sales. Filters on other tables, such as Year or Country, still apply to both numerator and denominator.

---

---

Example 15: Percent of the Visible Total with ALLSELECTED

Scenario: Users pick a few categories in a slicer. Each bar should show its share of the selected categories, not of all categories in the model.

Category Share of Selected %=
DIVIDE (
    [Total Sales],
    CALCULATE (
        [Total Sales],
        ALLSELECTED ( Product[Category] )
    )
)

How it evaluates: ALL would clear the Category filter completely, including the slicer. ALLSELECTED removes only the filters that come from the visual itself (the rows and columns) and restores the filters that come from outside it, such as slicers and report filters. The denominator is therefore the total of whatever categories the user selected, so the shares add up to 100% for that selection.

Example 16: Filtering with a Measure Using FILTER

Scenario: You want total sales from only those brands that individually exceed 1,000,000 in sales.

Sales from High-Volume Brands =
CALCULATE (
    [Total Sales],
    FILTER (
        VALUES ( Product[Brand] ),
        [Total Sales] > 1000000
    )
)

How it evaluates: A Boolean filter cannot contain a measure, so you use FILTER. It iterates the brands visible in the current context. For each brand, [Total Sales] is evaluated with context transition, and the brand is kept only if its sales exceed the threshold. The surviving brands become a filter on Product[Brand], and [Total Sales] is then calculated for them. The threshold is hard-coded here only for clarity; in a real report it would usually come from a parameter table.

Example 17: Comparing Two Columns of the Same Table

Scenario: Finance wants to know how much revenue came from order lines that were sold below cost.

Sales on Loss-Making Lines =
CALCULATE (
    [Total Sales],
    FILTER (
        Sales,
        Sales[SalesAmount] < Sales[Cost]
    )
)

How it evaluates: A Boolean filter argument can reference only one column, so it cannot compare SalesAmount to Cost. FILTER iterates the Sales rows that exist in the current context and keeps those where SalesAmount is less than Cost. The kept rows become the filter, and [Total Sales] adds up their amounts. Because FILTER iterates a fact table here, keep an eye on performance with very large data.

Example 18: Year-to-Date Sales

Scenario: A dashboard needs a running year-to-date figure that resets every January.

Sales YTD =
CALCULATE (
    [Total Sales],
    DATESYTD ( 'Date'[Date] )
)

How it evaluates: DATESYTD returns the dates from January 1 of the current year through the last date visible in the filter context. CALCULATE uses that set of dates as the new Date filter. In a monthly visual, the March value covers January through March. It works for any year shown in the visual.

Example 19: Same Period Last Year

Scenario: Managers want to see this month's sales next to the same month a year ago.

Sales Same Period Last Year =
CALCULATE (
    [Total Sales],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

How it evaluates: SAMEPERIODLASTYEAR takes the dates currently in context and returns the same dates shifted back one year. CALCULATE replaces the Date filter with that shifted set. In a visual showing March 2025, the measure returns March 2024 sales.

Example 20: Year-over-Year Growth % with Variables

Scenario: You need a growth percentage that is easy to read, debug, and reuse.

Sales YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PriorYearSales =
    CALCULATE (
        [Total Sales],
        SAMEPERIODLASTYEAR ( 'Date'[Date] )
    )
RETURN
    DIVIDE ( CurrentSales - PriorYearSales, PriorYearSales )

How it evaluates: Each variable is calculated once, in the filter context where the measure is called, and the result is stored. CurrentSales uses the visual's filters as they are. PriorYearSales uses CALCULATE to move the dates back a year. DIVIDE then returns the growth ratio, or blank if there are no prior-year sales. Variables keep the logic readable and avoid repeating the same calculation.

Example 21: Previous Month Sales with DATEADD

Scenario: A monthly report needs last month's sales for a month-over-month comparison.

Sales Previous Month =
CALCULATE (
    [Total Sales],
    DATEADD ( 'Date'[Date], -1, MONTH )
)

How it evaluates: DATEADD shifts every date in the current context back by one month. In a visual showing April, the filter becomes March and [Total Sales] is calculated for it. You can change the interval to DAY, QUARTER, or YEAR. DATEADD works best when the visual is at a clean granularity, such as full months.

Example 22: Rolling 3-Month Sales

Scenario: To smooth out seasonality, the finance team tracks sales over the trailing three months.

Sales Rolling 3 Months =
CALCULATE (
    [Total Sales],
    DATESINPERIOD (
        'Date'[Date],
        MAX ( 'Date'[Date] ),
        -3,
        MONTH
    )
)

How it evaluates: MAX('Date'[Date]) finds the last date visible in the current context, for example the last day of the current month in a monthly visual. DATESINPERIOD then returns a window of three months ending on that date. CALCULATE applies the window as the Date filter, so the measure adds up sales for the current month and the two before it.

Example 23: Fiscal Year-to-Date

Scenario: The company's fiscal year ends on June 30. Year-to-date must restart every July 1.

Sales Fiscal YTD =
CALCULATE (
    [Total Sales],
    DATESYTD ( 'Date'[Date], "6-30" )
)

How it evaluates: The optional second argument of DATESYTD tells DAX when the year ends. With "6-30", the accumulation starts on July 1 and runs to the last visible date. In a visual showing September, the measure covers July through September of the current fiscal year.

Example 24: Average Order Value for One Segment

Scenario: Sales leadership wants to see average order value for Corporate customers next to the overall figure.

Average Order Value =
DIVIDE ( [Total Sales], [Order Count] )

Corporate Average Order Value =
CALCULATE (
    [Average Order Value],
    Customer[Segment] = "Corporate"
)

How it evaluates: The first measure divides sales by distinct orders. The second wraps it in CALCULATE with a Segment filter. Both [Total Sales] and [Order Count] inside the ratio are evaluated for Corporate customers only, so you get a correct segment-level ratio rather than a ratio of unrelated numbers.

Example 25: Gap to a Growth Target

Scenario: The target for this year is last year's sales plus 10%. Management wants to see how far ahead or behind each period is.

Sales Gap to Growth Target =
VAR PriorYearSales =
    CALCULATE (
        [Total Sales],
        SAMEPERIODLASTYEAR ( 'Date'[Date] )
    )
VAR TargetSales = PriorYearSales * 1.10
RETURN
    [Total Sales] - TargetSales

How it evaluates: PriorYearSales shifts the dates back a year and calculates sales. TargetSales then adds 10% on top. The final line subtracts the target from current sales: a positive result means the period is ahead of target, a negative one means it is behind. The 10% is an assumption for this example. In a real model it would usually come from a target table or a parameter.

Example 26: Share of the Grand Total Ignoring Every Filter

Scenario: Whatever the user filters, each value should be shown as a share of total sales across the whole model.

Share of Grand Total % =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS () )
)

How it evaluates: Called with no arguments, REMOVEFILTERS clears every filter in the model, including slicers, report filters, and visual rows and columns. The denominator is therefore the all-time, all-products, all-customers total. Use it deliberately: unlike ALLSELECTED, it ignores the user's slicer selections, so the percentages will not add up to 100% when a slicer is active.

DAX CALCULATE Function: Complete Tutorial with 50 Examples

---

You build a simple measure. It adds up sales. It works perfectly, until someone asks, "Can we see that for last year only?" Or "What share of total is this product?" Or "How does it compare to the same period last year?"

Every one of those questions needs the same thing: the ability to change the question a measure is answering. In DAX, that ability has a name: the DAX CALCULATE function.

If you learn one function in DAX, make it this one. It is the engine behind time intelligence, percent-of-total calculations, rankings, dynamic segmentation, and most of the "how did they do that?" measures you see in polished reports.

In this DAX CALCULATE tutorial you will learn:

• What CALCULATE does and how its syntax works
• How filter context, row context, and context transition fit together
• How CALCULATE overrides, removes, and preserves existing filters
• 50 practical CALCULATE examples, from a one-line filter to expert-level patterns
• The mistakes that trip up even experienced developers, and how to avoid them

Everything here applies to CALCULATE in Power BI as well as SSAS Tabular and Azure Analysis Services models.

---

---

What Is the DAX CALCULATE Function?

CALCULATE evaluates an expression in a modified filter context. In plain terms: you give it a calculation, such as a sum of sales, plus instructions for changing the filters, and it runs the calculation under those new filters.

CALCULATE syntax

CALCULATE (
    <expression>,
    [<filter1>],
    [<filter2>],
    ...
)

• expression: the calculation to perform. It must return a single (scalar) value, such as a measure or an aggregation like SUM.
• filter arguments: optional. Each one adds, replaces, or removes filters before the expression is evaluated. There can be as many as you need.

The result is whatever the expression returns, computed under the filters you specified.

Why CALCULATE matters so much

CALCULATE and its sibling CALCULATETABLE are the functions built to modify filter context. Nearly every advanced DAX pattern depends on that ability. There is also a less obvious reason: every time you reference a measure inside another expression, DAX silently wraps it in a CALCULATE. That implicit wrapper is what powers context transition, which we cover below.

Understanding CALCULATE means understanding how DAX thinks. So let's start with the core concepts.

Core Concepts You Need Before the Examples

Filter context: the "current set of filters"

Filter context is the set of filters active when a DAX expression is evaluated. It comes from many places: slicers, report filters, rows and columns of a visual, and filter arguments inside CALCULATE.

Imagine a matrix with Category on rows and Year on columns. For the cell at "Bikes / 2024", the filter context is Category = Bikes and Year = 2024. A measure like [Total Sales] sees only the sales rows that survive those filters. The same measure returns a different number in every cell because the filter context is different in every cell.

Row context: "the current row"

Row context means DAX is working through a table one row at a time. You get a row context in two places: calculated columns, and iterator functions such as SUMX, AVERAGEX, and FILTER.

-- Row context inside an iterator
Total Margin Amount =
SUMX (
    Sales,
    Sales[SalesAmount] - Sales[Cost]
)

Here SUMX steps through each Sales row and evaluates the expression with that row's values available. A row context lets you reference columns of the current row. It does not filter the model. That distinction matters in the next section.

Context transition: turning a row into filters

Context transition is what happens when CALCULATE (or a measure reference, which is an implicit CALCULATE) is evaluated inside a row context. DAX converts the current row into equivalent filters on the columns of that row's table, and the expression is then evaluated under those filters.

Sales per Product Average =
AVERAGEX (
    Product,
    [Total Sales]
)

For each Product row, referencing [Total Sales] triggers context transition. The current product row becomes a filter, so [Total Sales] returns the sales of that product. AVERAGEX then averages those values. Without context transition, [Total Sales] would return the same grand total for every row.

Two details to remember:

• The transition filters by all columns of the iterated table. If the table has duplicate rows, the filter matches all of the duplicates.
• It is powerful but can be expensive on large tables, because it runs once per iterated row.

How CALCULATE modifies filters: override, remove, preserve

When you pass a filter argument to CALCULATE, it interacts with the existing filter context in one of three ways:

• Override (the default for column filters): a filter on a column replaces any existing filter on that same column. If a slicer selects Clothing and your measure says Product[Category] = "Bikes", the measure returns Bikes.
• Remove: modifiers such as ALL, ALLEXCEPT, ALLSELECTED, and REMOVEFILTERS clear filters instead of adding them.
• Preserve: KEEPFILTERS makes the new filter intersect with the existing one instead of replacing it.

Filters on different columns combine with AND logic. Filters on the same column are replaced, not combined, unless you use KEEPFILTERS.

The three kinds of filter arguments

1. Boolean expressions

Product[Category] = "Bikes"

This is the simplest form. A Boolean filter can reference only one column, and it cannot contain a measure. Behind the scenes, DAX treats it as a filter over the distinct values of that column, so it overrides any existing filter on that column.

2. Table expressions

FILTER ( ALL ( Product[Brand] ), Product[Brand] <> "Contoso" )

A table expression returns a table, and its values become filters on the columns it contains. Table filters are more flexible than Boolean filters because they can use measures, multiple columns, and complex logic. The trade-off is that they can be slower when they iterate a large table.

3. Filter modifier functions

These change how filters behave rather than adding a filter of their own: ALL, ALLEXCEPT, ALLSELECTED, REMOVEFILTERS, KEEPFILTERS, USERELATIONSHIP, and CROSSFILTER. Several of them can only be used inside CALCULATE (or CALCULATETABLE). REMOVEFILTERS is one example.

Evaluation order in plain language

The order in which CALCULATE does its work explains many "why did that return the wrong number?" moments. At a conceptual level:

1. The filter arguments are evaluated in the original filter context, meaning the context the measure was called in, before any changes are made.
2. If a row context exists, context transition converts it into filters.
3. Relationship modifiers (USERELATIONSHIP, CROSSFILTER) take effect, and filter-removal modifiers (the ALL family) clear the filters they target.
4. The new filters are applied, overriding or intersecting with what remains.
5. Finally, the expression is evaluated in the resulting filter context.

The practical takeaway: filter arguments see the outer context, while the expression sees the new context. The order in which you list filter arguments does not change the result, but nesting one CALCULATE inside another does, because the inner one is evaluated inside the outer one's new context.

The Sample Data Model Used in All 50 Examples

Every example uses the same simple star schema, so you can focus on the DAX instead of relearning the model each time.

• Sales (fact table): OrderID, ProductKey, CustomerKey, OrderDate, Quantity, SalesAmount, Cost. Each row is an order line, and Cost is the total cost of that line.
• Product: ProductKey, Category, Subcategory, Brand
• Customer: CustomerKey, Country, Segment
• Date: a proper date table with one row per day, marked as a date table. We assume it has the columns Date, Year, MonthNumber, and MonthName. Sales[OrderDate] has an active relationship to 'Date'[Date].

The relationships run one-to-many from Product, Customer, and Date into Sales. Category names such as "Bikes", "Accessories", and "Clothing", and values such as "United States" and "Corporate", are illustrative. Swap in your own.

Base measures (defined once, reused everywhere)

Total Sales =
SUM ( Sales[SalesAmount] )

Total Cost =
SUM ( Sales[Cost] )

Total Quantity =
SUM ( Sales[Quantity] )

Total Profit =
[Total Sales] - [Total Cost]

Profit Margin % =
DIVIDE ( [Total Profit], [Total Sales] )

Order Count =
DISTINCTCOUNT ( Sales[OrderID] )

A quick note on naming: build small, reusable base measures like these and let advanced measures reference them. It keeps formulas short, readable, and consistent.

Beginner CALCULATE Examples (1–12)

These examples cover the foundation: applying a filter, combining filters, and removing filters. If you are new to CALCULATE, work through these in order.

Example 1: Filter by a Single Value

Scenario: Management wants a card showing sales for the Bikes category only, regardless of what the rest of the report shows for category.

Bikes Sales =
CALCULATE (
    [Total Sales],
    Product[Category] = "Bikes"
)

How it evaluates: CALCULATE takes the current filter context, replaces any filter on Product[Category] with "Bikes", and then evaluates [Total Sales]. Filters on other columns, such as Year or Country, stay in place. This is the simplest and most common use of CALCULATE.

Example 2: Filter by Country

Scenario: The US sales team needs its own sales number on a shared dashboard.

US Sales =
CALCULATE (
    [Total Sales],
    Customer[Country] = "United States"
)

How it evaluates: The filter on Customer[Country] travels through the relationship from Customer to Sales, so only sales to US customers are included. Filters on a dimension table propagate to the fact table automatically, so you never need to touch the Sales table directly.

Example 3: Multiple Filters (AND Logic)

Scenario: You want sales of Bikes to Corporate customers only.

Corporate Bikes Sales =
CALCULATE (
    [Total Sales],
    Product[Category] = "Bikes",
    Customer[Segment] = "Corporate"
)

How it evaluates: Each filter argument applies to a different column, so they combine with AND. Only sales rows that match both Bikes and Corporate are included. The same applies to as many filters as you add.

Example 4: Multiple Values for One Column (OR Logic)

Scenario: A report needs sales for Bikes and Accessories combined.

Bikes and Accessories Sales =
CALCULATE (
    [Total Sales],
    Product[Category] IN { "Bikes", "Accessories" }
)

How it evaluates: The IN operator turns the filter into "Category is any of these values". Listing two separate Boolean filters on the same column would not work, because the second would replace the first. IN is the clean way to express OR for one column.

Example 5: Excluding a Value

Scenario: Leadership wants to see sales without the Accessories category, which behaves differently from the core business.

Sales Excluding Accessories =
CALCULATE (
    [Total Sales],
    Product[Category] <> "Accessories"
)

How it evaluates: The filter keeps every category value except "Accessories" and replaces any existing filter on Category. Note that this replacement means a slicer that selects only Bikes would be overridden, and the measure would return all categories except Accessories. If you want the exclusion to respect the slicer, you need KEEPFILTERS, which appears in the intermediate section.

Example 6: Filtering on a Numeric Column

Scenario: The operations team wants to know how much revenue comes from bulk order lines, defined as five or more units.

Bulk Line Sales =
CALCULATE (
    [Total Sales],
    Sales[Quantity] >= 5
)

How it evaluates: The Boolean filter selects every Quantity value that is 5 or higher, which in turn selects the matching rows in the Sales table. [Total Sales] then adds up only those rows. Comparison operators work in Boolean filters just like equality does.

Example 7: Overriding the Date Filter with a Fixed Year

Scenario: A KPI card must always show 2024 sales as a fixed benchmark, even when users change the year slicer.

Sales 2024 =
CALCULATE (
    [Total Sales],
    'Date'[Year] = 2024
)

How it evaluates: The filter on 'Date'[Year] replaces whatever year the user selected. Other filters, such as Category or Country, still apply. This override behavior is the basis for most time-comparison measures.

Example 8: Removing a Filter with ALL

Scenario: You need the total sales across all categories, even inside a visual that splits sales by category.

Sales All Categories =
CALCULATE (
    [Total Sales],
    ALL ( Product[Category] )
)

How it evaluates: ALL removes any filter on Product[Category] before [Total Sales] is evaluated. In a visual with a row per category, every row now shows the same all-categories total. Filters on other columns, such as Year or Country, are still respected.

Example 9: Percent of Total Across Categories

Scenario: A bar chart should show each category's share of overall sales.

Category Share of Sales % =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], ALL ( Product[Category] ) )
)

How it evaluates: The numerator uses the current context, so it is the sales for each category. The denominator removes the Category filter and returns the all-category total. DIVIDE returns the ratio and handles division by zero safely. Because the denominator only clears Category, a Year slicer still limits both numbers consistently.

Example 10: Removing All Filters from a Table with REMOVEFILTERS

Scenario: You need total sales regardless of any customer-related filter, so you can compare a selected customer group against the whole customer base.

Sales All Customers =
CALCULATE (
    [Total Sales],
    REMOVEFILTERS ( Customer )
)

How it evaluates: REMOVEFILTERS clears filters from every column of the Customer table, including Country and Segment. Filters from other tables, such as Product and Date, stay active. REMOVEFILTERS is the more readable modern way to express "clear filters" and can only be used as a CALCULATE modifier.

Example 11: Using CALCULATE with Any Measure

Scenario: You want the profit margin for Bikes only.

Bikes Profit Margin % =
CALCULATE (
    [Profit Margin %],
    Product[Category] = "Bikes"
)

How it evaluates: The expression is a measure, not a column aggregation. When CALCULATE changes the filter context, every measure referenced inside [Profit Margin %] sees the new filter. Total Profit and Total Sales are therefore both computed for Bikes before the ratio is taken. This is why small, reusable base measures pay off.

Example 12: Counting Distinct Orders with a Filter

Scenario: The brand manager for Contoso wants to know how many distinct orders included a Contoso product.

Contoso Orders =
CALCULATE (
    [Order Count],
    Product[Brand] = "Contoso"
)

How it evaluates: The filter on Product[Brand] flows to Sales, leaving only order lines for Contoso products. DISTINCTCOUNT on OrderID then counts each order once, even if it contains several Contoso lines. CALCULATE works with any aggregation, not just SUM.

SUM vs SUMX in DAX: 20 Examples and When to Use Each

---

You write a measure with SUM, and it works. Then someone asks for revenue before discounts, and SUM suddenly can't help. You switch to SUMX, the numbers appear, and you are left wondering why one function worked and the other didn't. If that sounds familiar, you are in good company. The SUM vs SUMX question trips up beginners and experienced developers alike.

The short version: SUM adds up one column, and SUMX evaluates an expression for every row of a table and then adds up the results. This article explains that difference in plain language, shows when each function is the right choice, and walks through 20 examples from beginner to advanced. Along the way we will cover the DAX SUMX function, row context in DAX, context transition, and SUM vs SUMX performance.

The Sample Data Model Used in This Article

Every example uses the same small star schema, so you can focus on the DAX instead of learning a new model each time.

• Customer (dimension): CustomerKey, CustomerName, Segment, City, Country, Region, and more.
• Product (dimension): ProductKey, ProductName, Category, Subcategory, UnitCost, ListPrice, and more.
• Date (dimension, marked as the date table): Date, Year, MonthNumber, MonthName, MonthYear, MonthYearSort, and more.
• Sales (fact, one row per order): SalesOrderID, CustomerKey, OrderDate, ShipDate, SalesChannel, Freight, TaxAmount.
• Sales Order Details (fact, one row per product per order): SalesOrderID, ProductKey, Quantity, UnitPrice, DiscountPct, SalesAmount, TotalCost.

All relationships are one-to-many and single direction. Customer and Date filter Sales, and Sales filters Sales Order Details. Product filters Sales Order Details directly. The Date to Sales[OrderDate] relationship is active, and Date to Sales[ShipDate] is inactive.

Important: Freight and TaxAmount live on the order header (Sales). Product does not filter Sales, so you should never slice header-level amounts by Product columns. We will flag this again when it matters.

These are the base measures reused throughout the article:

Total Sales =
SUM ( 'Sales Order Details'[SalesAmount] )

Total Quantity =
SUM ( 'Sales Order Details'[Quantity] )

Total Freight =
SUM ( Sales[Freight] )

Every other measure is introduced right before its example. The thresholds, rates, and caps in the examples are illustrative business assumptions, not columns in the model.

---

---

Understanding the Concepts

SUM: An Aggregator Over a Single Column

SUM takes one column and adds up its values in the current filter context. That is all it does. It cannot multiply two columns, look at a related table, or apply an IF to each row. Conceptually, you can think of it as a shortcut for summing one column of a table.

SUM ( <ColumnName> )

SUMX: An Iterator That Evaluates Row by Row

SUMX is one of the DAX iterator functions. It takes a table and an expression. For each row of that table, it evaluates the expression, then adds up all the results. Other members of the iterator family include AVERAGEX, MINX, MAXX, COUNTX, and RANKX. They all share the same pattern: a table first, an expression second.

SUMX ( <Table>, <Expression> )

Row Context in DAX

Row context means "the current row." When SUMX walks through a table, it creates a row context for each row, and column references in the expression return that row's values. This is why 'Sales Order Details'[Quantity] * 'Sales Order Details'[UnitPrice] works inside SUMX: for each line, it multiplies that line's quantity by that line's price.

Outside an iterator, a bare column reference in a measure has no row context, so DAX does not know which row you mean. SUM avoids the problem because it aggregates the whole column in the filter context instead of reading one row at a time.

Context Transition Inside SUMX

Here is a subtle but important point. A plain column reference inside an iterator uses row context. A measure reference inside an iterator behaves differently, because every measure is wrapped in an implicit CALCULATE. CALCULATE converts the current row into filters, and this is called context transition.

For example, if you iterate over the Customer table and call [Total Sales], DAX filters the model to the current customer before evaluating the measure. That is how you get per-customer results inside one measure. It is powerful, and it is also where performance problems usually start, since the measure is re-evaluated for every iterated row.

When to Choose SUM and When SUMX Is Required

Choose SUM When

• You are adding up a single column that already holds the value you need, such as SalesAmount, Quantity, or Freight.
• Any filtering you need can be handled by CALCULATE or by slicers.

SUMX Is Required When

• Calculations span multiple columns: Quantity * UnitPrice, or SalesAmount - TotalCost evaluated line by line.
• You need columns from related tables: for example, multiplying Quantity by Product[ListPrice] using RELATED.
• Row-level conditional logic applies: different treatment for each row, such as IF or SWITCH.
• You want a custom grain: compute something per category, per customer, or per product first, then add the results.

SUM vs SUMX: Comparison Table

Aspect	SUM	SUMX
Purpose	Add up the values of one column	Evaluate an expression per row, then add the results
Input	A single column	A table and an expression
Row context	None created	Created for each row of the table
Flexibility	Low: one column only	High: multiple columns, related tables, logic, custom grain
Typical performance	Very fast and simple	Often fast for simple column math; slower when measures or complex logic run per row

Rule of thumb: use SUM when it can do the job, and reach for SUMX when you need a calculation at row level or at a custom grain.

20 SUM and SUMX Examples

Beginner Examples (1-6)

Example 1: Basic SUM

Scenario: Management wants total revenue.

Total Sales =
SUM ( 'Sales Order Details'[SalesAmount] )

This is our base measure. SalesAmount already holds Quantity x UnitPrice x (1 - DiscountPct) for every line, so a plain SUM is enough. Slicers on Customer, Product, or Date filter it automatically. Whenever a stored column already contains the number you need, start here.

Example 2: SUM with a CALCULATE Filter

Scenario: The sales director wants revenue from Corporate customers only.

Corporate Sales =
CALCULATE ( [Total Sales], Customer[Segment] = "Corporate" )

CALCULATE changes the filter context before [Total Sales] runs. The filter on Customer flows through Sales into Sales Order Details, so no iteration is needed. This is the standard way to apply a fixed filter to a SUM-based measure.

Example 3: SUM of Quantity

Scenario: The warehouse team wants to know how many units were sold.

Total Quantity =
SUM ( 'Sales Order Details'[Quantity] )

Another simple aggregation over one column. Units sold are a different story from revenue, so keeping a separate Total Quantity measure makes later ratios easier. We reuse it in example 17.

Example 4: SUM of Freight

Scenario: Finance wants to track shipping costs.

Total Freight =
SUM ( Sales[Freight] )

Freight sits on the order header, one row per order. Watch out: Product does not filter Sales, so putting Product[Category] on a visual next to Total Freight repeats the same overall freight on every row. Slice freight by Customer, Date, or SalesChannel instead.

Example 5: A SUMX That Matches SUM

Scenario: You want to see that SUMX with a single column reproduces SUM.

Total Sales (SUMX) =
SUMX ( 'Sales Order Details', 'Sales Order Details'[SalesAmount] )

SUMX visits each order line, reads its SalesAmount, and adds the values, so the result equals Total Sales. This example is for learning only. The engine often handles both forms efficiently, but SUM states your intent more clearly and is the better choice in production.

Example 6: First Real SUMX with Quantity x UnitPrice

Scenario: The pricing team asks how much revenue we would have earned with no discounts.

Gross Sales =
SUMX (
    'Sales Order Details',
    'Sales Order Details'[Quantity] * 'Sales Order Details'[UnitPrice]
)

No single column holds quantity times price, so SUM cannot do this. SUMX creates a row context for each line, multiplies the two columns, and adds the products. Summing Quantity and UnitPrice separately and multiplying the totals would give a wrong answer. This is the classic reason SUMX exists.

Intermediate Examples (7-14)

Example 7: Discount Amount

Scenario: Leadership wants to know how much revenue was given away in discounts.

Discount Amount =
SUMX (
    'Sales Order Details',
    'Sales Order Details'[Quantity]
        * 'Sales Order Details'[UnitPrice]
        * 'Sales Order Details'[DiscountPct]
)

Discount % of Gross Sales =
DIVIDE ( [Discount Amount], [Gross Sales] )

Each line's discount is quantity x price x discount percentage, summed across lines. As a sanity check, [Gross Sales] - [Total Sales] should return the same figure. The second measure expresses the discount as a share of gross sales, using DIVIDE to avoid divide-by-zero errors.

Example 8: SUMX with RELATED

Scenario: What would revenue be if every unit sold at today's list price?

Sales at List Price =
SUMX (
    'Sales Order Details',
    'Sales Order Details'[Quantity] * RELATED ( Product[ListPrice] )
)

ListPrice lives in Product, not in the fact table. RELATED follows the many-to-one relationship from each order line to its product and fetches the price. RELATED only works inside a row context, which SUMX provides. Comparing this measure with Gross Sales shows how far historical prices drifted from the current price list.

Example 9: SUMX with IF per Row

Scenario: A 5% volume rebate applies to order lines of 10 units or more.

Net Sales After Volume Rebate =
SUMX (
    'Sales Order Details',
    'Sales Order Details'[SalesAmount]
        * IF ( 'Sales Order Details'[Quantity] >= 10, 0.95, 1 )
)

The condition is evaluated separately for each line, which is something SUM cannot do. Lines with 10 or more units keep 95% of their sales amount, and all other lines keep 100%. The 5% rate and the threshold of 10 are made-up business rules for this example.

Example 10: SUMX over a Filtered Table

Scenario: Sales wants revenue from large order lines only, those worth at least 1,000.

High-Value Line Sales =
SUMX (
    FILTER (
        'Sales Order Details',
        'Sales Order Details'[SalesAmount] >= 1000
    ),
    'Sales Order Details'[SalesAmount]
)

FILTER returns only the lines that pass the test, and SUMX adds up the survivors. The condition compares a value on each row, which a simple CALCULATE filter cannot express as neatly. Be aware that FILTER over a large fact table can be costly, so use it only when the row-level test is genuinely needed.

Example 11: SUMX over VALUES(Product[Category])

Scenario: Commission is paid on category sales, but each category is capped at 500,000.

Commissionable Sales (Capped per Category) =
SUMX (
    VALUES ( Product[Category] ),
    VAR CategorySales = [Total Sales]
    RETURN
        IF ( CategorySales > 500000, 500000, CategorySales )
)

VALUES returns the categories visible in the current filter context, giving us a custom grain. For each category, context transition makes [Total Sales] return that category's sales, and the IF applies the cap. The results are then added. The key point is that the cap applies per category, then the totals are added, so the grand total is not capped as a whole.

Example 12: Profit per Line at Standard Cost

Scenario: Finance wants profit if each unit had cost today's standard UnitCost.

Profit at Standard Cost =
SUMX (
    'Sales Order Details',
    VAR StandardCost =
        'Sales Order Details'[Quantity] * RELATED ( Product[UnitCost] )
    RETURN
        'Sales Order Details'[SalesAmount] - StandardCost
)

The variable calculates the standard cost of each line, and the expression subtracts it from that line's SalesAmount. Our base Total Profit uses the stored TotalCost column instead, so this measure answers a different question. Variables keep row-level logic readable and avoid repeating the same calculation.

Example 13: Channel-Based Commission with RELATED on the Header

Scenario: Partners and retail stores earn different commission rates than online sales.

Estimated Channel Commission =
SUMX (
    'Sales Order Details',
    'Sales Order Details'[SalesAmount]
        * SWITCH (
            RELATED ( Sales[SalesChannel] ),
            "Online", 0.02,
            "Retail", 0.03,
            "Partner", 0.08,
            0
        )
)

SalesChannel lives on the order header, but each order line can reach it through RELATED. SWITCH picks a rate for each line based on its channel, and SUMX adds the commission amounts. The rates are illustrative. This pattern works whenever a line-level amount needs a rule driven by a header attribute.

Example 14: Average Monthly Sales with AVERAGEX

Scenario: Executives want the average sales per month for whatever period is selected.

Average Monthly Sales =
AVERAGEX (
    VALUES ( 'Date'[MonthYear] ),
    [Total Sales]
)

AVERAGEX is SUMX's sibling: it evaluates an expression per row and averages the results instead of adding them. Here the rows are the months in the current filter context, and each month's [Total Sales] comes from context transition. Months with no sales return blank and are ignored by AVERAGEX, which is usually what you want for an average.

Advanced Examples (15-20)

Example 15: SUMX over Customer with a Threshold

Scenario: Count only the revenue from key accounts, customers who spent more than 10,000.

Key Account Sales =
SUMX (
    Customer,
    VAR CustomerSales = [Total Sales]
    RETURN
        IF ( CustomerSales > 10000, CustomerSales, 0 )
)

Iterating over the Customer table gives us one row per customer. For each customer, context transition computes their total sales, and the IF keeps only those above the threshold. The grain here is the customer, which cannot be reached with a simple SUM. The 10,000 threshold is an assumption for illustration.

Example 16: SUMX over SUMMARIZE

Scenario: Show sales only from category and channel combinations that exceeded 50,000.

Sales in Strong Category-Channel Combinations =
SUMX (
    FILTER (
        SUMMARIZE (
            'Sales Order Details',
            Product[Category],
            Sales[SalesChannel]
        ),
        [Total Sales] > 50000
    ),
    [Total Sales]
)

SUMMARIZE builds a small table with one row per category and channel combination that exists in the data. FILTER keeps the combinations above 50,000, and SUMX adds their sales. Because the iterated table is small, this is far cheaper than looping over millions of order lines. Both measures inside the iterator rely on context transition.

Example 17: Weighted Average Price

Scenario: What is the true average unit price, taking quantities into account?

Weighted Average Unit Price =
DIVIDE (
    SUMX (
        'Sales Order Details',
        'Sales Order Details'[Quantity] * 'Sales Order Details'[UnitPrice]
    ),
    [Total Quantity]
)

A simple average of UnitPrice treats a line of 1 unit the same as a line of 500 units. The weighted version divides total price-times-quantity by total units, so large lines count for more. This is the price before discounts. It is the same as dividing [Gross Sales] by [Total Quantity].

Example 18: SUMX with TOPN

Scenario: How much of our revenue comes from the five best-selling products?

Top 5 Products Sales =
SUMX (
    TOPN ( 5, VALUES ( Product[ProductName] ), [Total Sales] ),
    [Total Sales]
)

TOPN ranks the visible products by [Total Sales] and returns the top five. SUMX then adds up their sales, using context transition for each product. If two products tie for fifth place, TOPN can return more than five rows, so keep that in mind. Divide this by [Total Sales] if you want the top-five share.

Example 19: Running Total

Scenario: Show cumulative sales from the start of the data up to each date.

Running Total Sales =
VAR LastVisibleDate = MAX ( 'Date'[Date] )
RETURN
    SUMX (
        FILTER ( ALL ( 'Date' ), 'Date'[Date] <= LastVisibleDate ),
        [Total Sales]
    )

The variable captures the latest date in the current context. ALL removes the existing date filters, FILTER keeps every date up to that point, and SUMX adds up each day's sales. Because ALL ignores date slicers, this measure always starts from the beginning of the data. It iterates over every day, so test it on large date ranges.

Example 20: A Performance-Aware Rewrite

Scenario: An existing report calculates online revenue by iterating over every order line.

-- Before: iterates the large fact table and checks the channel on every row
Online Sales (Slow) =
SUMX (
    FILTER (
        'Sales Order Details',
        RELATED ( Sales[SalesChannel] ) = "Online"
    ),
    'Sales Order Details'[SalesAmount]
)

-- After: a simple filter on a column and a plain SUM
Online Sales =
CALCULATE ( [Total Sales], Sales[SalesChannel] = "Online" )

Both versions return the same result. The first walks the large fact table and tests each line, while the second applies a column filter and lets the engine do a simple aggregation. The same logic applies to anything that recomputes a stored column, such as multiplying Quantity, UnitPrice, and 1 - DiscountPct when SalesAmount already holds that value. If SUM and CALCULATE can do the job, prefer them.

Common Mistakes and Performance Tips

• Using SUMX when SUM is enough. SUMX over a single column, as in example 5, adds complexity with no benefit. Use SUM for a plain column total.
• Iterating over a large fact table when a smaller table would work. If you only need results per category, iterate over VALUES ( Product[Category] ) instead of every order line, as in examples 11 and 16.
• Calling measures inside an iterator over a big table. Context transition runs once per row. Iterating over Sales Order Details with a measure inside can be slow, so prefer simple column math there and keep measure-based iteration for small tables.
• Using FILTER over a whole fact table for a simple condition. A column filter inside CALCULATE is usually lighter than FILTER ( 'Sales Order Details', ... ), as example 20 shows.
• Forgetting how totals behave. A SUMX over VALUES applies logic per item, then adds. Grand totals are the sum of the item results, not a recalculation on the whole, which matters for caps and thresholds. Also remember that header-level amounts like Freight are not filtered by Product.

Conclusion: Key Takeaways

The SUM vs SUMX choice comes down to one question: does a column already hold the value you want to add up?

• SUM adds up one column in the current filter context. It is simple, readable, and fast.
• SUMX is an iterator. It creates a row context, evaluates an expression for each row, and adds the results.
• Use SUMX for multi-column math, related-table columns via RELATED, row-level logic, and custom grains.
• Measures inside an iterator trigger context transition, which is powerful but costly on large tables.
• Iterate over the smallest table that gives the right grain, and rewrite SUMX as SUM with CALCULATE whenever possible.

Once you can tell when you need a row context and when you don't, most of the confusion around iterators goes away.

Want to Go Deeper?

Ready to master DAX, data modeling, and SSAS Tabular? Explore more step-by-step tutorials, practical examples, and best-practice guides at exceltobi.com.

Calculated Columns vs Calculated Tables

Picture a developer who needs a margin figure in a report. She adds a new column to the Product table and types a ratio. Every row looks right. Then she drops the column into a matrix, and the Grand Total shows a number nobody can explain.

Now picture the opposite problem. A model has grown heavy and slow to refresh because every new idea became another column. Nobody remembers why half of them exist.

Both stories come from the same gap: not knowing what calculated columns and calculated tables really do, and when a measure is the better tool. This guide closes that gap. You will learn how each object works, why you sometimes cannot avoid them, how to create them in several tools, and how to review them with 20 worked examples.

Meet the Sample Data Model

Every example uses one model: Customer, Product and Date dimensions, plus two fact tables, Sales (one row per order) and Sales Order Details (one row per order line). Customer and Date filter Sales, which filters Sales Order Details. Product filters Sales Order Details only. The inactive Date relationship on Sales[ShipDate] is activated with USERELATIONSHIP.

One warning up front: header-level amounts such as Sales[Freight] and Sales[TaxAmount] are not filtered by Product, so never slice them by Product columns. The examples below do not do this. These base measures are used 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 Quantity = SUM ( 'Sales Order Details'[Quantity] )

Order Count = DISTINCTCOUNT ( Sales[SalesOrderID] )

The Concepts, Explained Simply

What Is a Calculated Column?

A DAX calculated column adds a new column to a table that already exists. Think of adding a column to a spreadsheet and filling the formula down: each row gets its own answer. DAX evaluates the formula row by row, using row context, when data is processed or refreshed. The results are stored in the model like any other column.

What Is a Calculated Table?

A DAX calculated table is a DAX expression that returns a whole table. Think of printing a helper sheet from your workbook and keeping it. The expression runs when data is processed or refreshed, and the resulting table is stored in the model. After that it behaves like a normal table.

The Third Option: Measures

A measure is evaluated at query time, in filter context, and nothing is stored except the formula. It is a calculator that works on whatever the user has selected. (I will cover explicit measures in other articles, so I will stay brief here.)

Refresh Time vs Query Time

Calculated columns and tables pay their cost at refresh: they add refresh work and memory, but their values are ready when a report opens. Measures pay at query time: they add almost no storage, but the engine computes them whenever a visual asks. Neither is automatically faster. The trade-off is storage versus calculation at query time, and the outcome depends on your model.

Row Context vs Filter Context

Row context means "the current row". It exists inside a calculated column and inside iterators such as SUMX. Filter context means "the filters currently applied", from slicers, visuals and CALCULATE. A calculated column has no filter context from the report, because it is computed at refresh.

That raises a question: how can a column use a measure? The answer is context transition. When a measure, or CALCULATE, is evaluated inside row context, DAX turns the current row's values into filters. The measure then sees only that row's slice of the data. Examples 9 and 10 use this.

Calculated Objects vs Power Query and the Source

Power Query (M) transformations run during refresh, before data lands in the model. They cannot use model relationships or DAX measures. Columns created in the source system, such as a SQL view, are calculated once and shared with every consumer of that source. Calculated objects run after loading, so they can use relationships, other tables and measures. The price is that the logic lives inside the model.

Why Calculated Columns and Tables Are Necessary

A measure returns a value in a cell. It cannot become a category. That is the dividing line, and it explains most legitimate uses:

• Slicers, axes, headers, grouping and bands. A field like age group must exist as a column to appear on an axis or in a slicer.
• Relationships. Relationships connect columns, so a composite key or a cleaned key must be a real column.
• Sort by column. A text column such as a band name can be sorted by another column in the same table, which must also exist physically.
• Tables with no source. A date table, a disconnected slicer table, a parameter table, or a role-playing copy of a dimension can all be built with DAX.
• Helper or summary tables. Sometimes a pre-calculated table is worth its cost (see Example 18).

Support for calculated columns and tables in DirectQuery and composite models, and in particular SSAS compatibility levels, has restrictions that change over time. If you work in those modes, check the current documentation before you design around them.

Comparison: Calculated Column vs Calculated Table vs Measure

Aspect	Calculated column	Calculated table	Measure
What it returns	One value per row of an existing table	A whole table (rows and columns)	A single value for the current filter context
When evaluated	At data refresh or processing	At data refresh or processing	At query time
Where stored	Values stored in the model, inside its table	Result stored in the model as a new table	Only the formula is stored, not the result
Context	Row context (filter context only via context transition)	No report filter context; row context inside iterators	Filter context from visuals, slicers and filters
Memory impact	Grows with row count and column cardinality	Grows with the rows and columns returned	Negligible storage; cost is computing at query time
Usable in slicers or axes	Yes	Yes (its columns)	No, not as a category (it appears as a value)
Usable in relationships	Yes, as a key column	Yes, its columns can take part	No
Responds to slicer selections	No, fixed at refresh (though you can slice by it)	No, static until refresh	Yes
Best use	Row-level attributes: groups, bands, keys, sort columns	Date tables, parameter tables, helper and role-playing tables	Aggregations, ratios, and anything that must react to selections

---

---

When to Use Which: A Decision Guide

A simple rule of thumb, in order of preference:

1. Source system or Power Query for row-level data shaping.
2. A measure for aggregations and ratios.
3. A calculated column or table only when the model itself must hold the result.

Need a value that reacts to slicers and visuals?
  YES -> Use a measure.
  NO  -> Need it as a slicer, axis, band, key or sort column?
          YES -> Can the source or Power Query create it cleanly?
                  YES -> Create it there.
                  NO  -> Calculated column (needs relationships/measures,
                         or the source is not under your control).
          NO  -> Need a whole table (date, parameter, helper)?
                  YES -> Calculated table (or a source/M table).
                  NO  -> Probably a measure.

There are exceptions. If you cannot touch the source, or the logic needs model relationships, a calculated column is a sensible choice. In SSAS Tabular, a calculated column may be the simplest option when no one owns the upstream ETL.

Why Ratios Belong in Measures

A ratio is a relationship between two totals, not a property of a row. If you store a ratio per row and then sum it, the result is meaningless. If you average it, every row counts equally regardless of its sales volume. A measure like [Profit Margin %] divides the totals at whatever level the visual shows, so it is correct in every cell, including the Grand Total. Example 12 proves this.

Different Ways to Create Calculated Columns and Tables

1. Power BI Desktop

Select a table in the Fields pane, then use the table tools in the ribbon: New column or New table. Type the DAX in the formula bar. Pros: it is fast, and you get immediate feedback. Cons: the logic lives inside the file, so it is harder to review, version and manage in bulk.

2. Visual Studio with Analysis Services Projects (SSAS Tabular)

In a Tabular model project, the model designer lets you enter a DAX formula for a new calculated column in a table, and create calculated tables from the table-related menus. Exact labels differ between versions, so check the current documentation. Pros: projects fit source control and deployment pipelines. Cons: the tooling is heavier, and you must deploy and process to see the result.

3. Tabular Editor

Tabular Editor is an external tool for editing tabular models. In general terms, you right-click a table, add a calculated column or calculated table, and edit the DAX in an expression editor. Pros: bulk edits, scripting and a clearer view of dependencies. Cons: it is a separate tool, and editions and licensing differ, so check the current details.

4. Scripting and Code-Based Definitions

Models can also be defined as text. TMSL describes SSAS Tabular models as JSON, and TMDL is a text-based format for semantic models. The DAX query view in Power BI Desktop can also help you write and test DAX. Availability and syntax change between releases, so confirm them in the current documentation before relying on any of these. Pros: repeatable, reviewable, and easy to diff. Cons: a steeper learning curve.

5. Power Query or the Source as the Alternative

In Power Query, use the Add Column options, or write M directly. In the source, create the column in a SQL view or ETL step. Pros: the logic runs before the model, can be shared by other consumers, and keeps DAX lean. Cons: you need access to the source, and neither option can use DAX measures or model relationships.

Performance, Refresh and Governance Notes

• Calculated objects add to refresh time and to memory. How much depends on your model.
• High-cardinality calculated columns (many distinct values) are costly because they compress poorly.
• Calculated objects can depend on each other. The engine works out the order, but long chains are hard to debug, and a circular dependency causes an error.
• Keep logic in one place. If the same rule appears in the source, in M and in DAX, one of them will drift.
• Document every calculated object: purpose, owner, and what it depends on.

20 Examples Based on the Sample Data Model

Each example adds a new object to the sample model. I name the table before every example.

Beginner: Calculated Columns (1-6)

Example 1: Customer Age Group

Scenario: Marketing wants to slice sales by age band. New calculated column in the Customer table.

Age Group =
VAR ReferenceDate = DATE ( 2026, 12, 31 )
VAR AgeInYears = INT ( YEARFRAC ( Customer[BirthDate], ReferenceDate, 1 ) )
RETURN
    IF (
        ISBLANK ( Customer[BirthDate] ),
        "Unknown",
        SWITCH (
            TRUE (),
            AgeInYears < 25, "Under 25",
            AgeInYears < 35, "25-34",
            AgeInYears < 50, "35-49",
            AgeInYears < 65, "50-64",
            "65+"
        )
    )

Explanation: SWITCH ( TRUE (), ... ) tests each condition in order and returns the first match. I used a fixed reference date because TODAY () in a stored column is evaluated only at refresh, so ages silently go stale between refreshes. A fixed date, or a date taken from your Date table, makes the result predictable. I avoided DATEDIFF with YEAR because it counts calendar-year boundaries, not full years. Use this pattern whenever bands must appear on a slicer or axis.

Example 2: Shipping Days

Scenario: Operations wants to see how long orders take to ship. New calculated column in the Sales table.

Shipping Days =
IF (
    ISBLANK ( Sales[ShipDate] ),
    BLANK (),
    DATEDIFF ( Sales[OrderDate], Sales[ShipDate], DAY )
)

Explanation: DATEDIFF returns the number of day boundaries between the two dates. The ISBLANK check keeps unshipped orders from producing a misleading number. Because it is a column, you can group orders by shipping days or use it in a filter. If you only need the average, a measure over this column is enough.

Example 3: Is Delayed Shipment

Scenario: A manager wants a quick True/False filter for late deliveries. New calculated column in the Sales table, built on Example 2.

Is Delayed Shipment =
IF (
    ISBLANK ( Sales[Shipping Days] ),
    BLANK (),
    Sales[Shipping Days] > 7
)

Explanation: The column returns TRUE, FALSE or blank, and the seven-day threshold is only an example. Flags like this work well as slicers and filters. Note that this column depends on Sales[Shipping Days]. Keep the threshold in a documented place if it may change.

Example 4: Margin Per Unit

Scenario: The pricing team wants the list-price profit per unit for each product. New calculated column in the Product table.

Margin Per Unit = Product[ListPrice] - Product[UnitCost]

Explanation: This is a row-level subtraction on one table, which is a legitimate use of a column. It describes the product itself, not any sales activity. Summing it across products would not mean much, so treat it as an attribute rather than something to total. Ideally this would be computed upstream if the source offers it.

Example 5: Price Band

Scenario: Merchandisers want to compare products grouped by price range. New calculated column in the Product table.

Price Band =
SWITCH (
    TRUE (),
    Product[ListPrice] < 25, "Budget",
    Product[ListPrice] < 100, "Standard",
    Product[ListPrice] < 500, "Premium",
    "Luxury"
)

Explanation: Products are bucketed by list price, and the band edges are illustrative. The column has only four distinct values, so it is cheap and compresses well. It can go straight onto a slicer or an axis. To keep the bands in a sensible order, add a numeric sort column and use Sort by column.

Example 6: Customer Label

Scenario: A report needs a readable label to tell apart customers who share a name. New calculated column in the Customer table.

Customer Label = Customer[CustomerName] & " (" & Customer[City] & ")"

Explanation: The & operator joins text, so "Maria Lopez (Madrid)" becomes one string. A blank city would leave empty parentheses, so handle that if your data has gaps. This column has high cardinality because it is close to unique per customer. Use it for display, and hide it if it is only a helper.

Intermediate: Relationships and Context Transition (7-11)

Example 7: Category on Order Lines

Scenario: An analyst wants the product category available directly on each order line. New calculated column in the 'Sales Order Details' table.

Category = RELATED ( Product[Category] )

Explanation: RELATED follows a relationship from the many side to the one side. Each order line has exactly one product, so DAX knows which Product row to read. It would not work the other way around, because one product has many lines. Use it sparingly: you can already slice by Product[Category], so this copy mostly costs memory.

Example 8: Gross Amount

Scenario: Finance wants to see the value of each line before discount. New calculated column in the 'Sales Order Details' table.

Gross Amount =
'Sales Order Details'[Quantity] * 'Sales Order Details'[UnitPrice]

Explanation: The stored SalesAmount applies DiscountPct, so Gross Amount minus SalesAmount equals the discount given on that line. The column is easy to understand, but the fact table is the largest in the model. A SUMX measure over Quantity * UnitPrice can give the same total without storing a value per row. Choose the column only if you need the value for slicing or row-level filtering.

Example 9: Order Sales

Scenario: Sales managers want the total value of each order on the order header. New calculated column in the Sales table.

Order Sales = CALCULATE ( [Total Sales] )

Explanation: Inside a calculated column, CALCULATE triggers context transition: the current Sales row becomes a filter. That filter flows from Sales to Sales Order Details through the relationship, so [Total Sales] sums only that order's lines. This works because SalesOrderID is unique per Sales row. The value is fixed at refresh and does not change with slicers.

Example 10: Customer Lifetime Sales

Scenario: CRM wants each customer's all-time sales stored on the customer row. New calculated column in the Customer table.

Lifetime Sales = CALCULATE ( [Total Sales] )

Explanation: Context transition turns the customer row into a filter, which flows Customer to Sales to Sales Order Details. The result is every sale for that customer. It is fixed at refresh and does not respond to a Date slicer, so it is a snapshot, not a live figure. If you need sales for the selected period, use [Total Sales] directly.

Example 11: Customer Tier

Scenario: The loyalty team wants to group customers into tiers. New calculated column in the Customer table, based on Example 10.

Customer Tier =
IF (
    ISBLANK ( Customer[Lifetime Sales] ),
    "No Sales",
    SWITCH (
        TRUE (),
        Customer[Lifetime Sales] >= 50000, "Platinum",
        Customer[Lifetime Sales] >= 20000, "Gold",
        Customer[Lifetime Sales] >= 5000, "Silver",
        "Bronze"
    )
)

Explanation: The thresholds are illustrative. This column depends on Customer[Lifetime Sales], which depends on [Total Sales], which depends on Sales Order Details columns. That is a dependency chain. It is fine here, but if you create a loop (for example, making Lifetime Sales depend on Customer Tier), the model will report a circular dependency. Hide Lifetime Sales if users only need the tier.

The Trap (12)

Example 12: Margin % as a Column vs the Measure

Scenario: A developer stores a margin ratio per product, then averages it in a visual. New calculated column in the Product table (plus a demonstration measure that exists only for this example).

Margin % = DIVIDE ( Product[ListPrice] - Product[UnitCost], Product[ListPrice] )

Avg of Margin % Column = AVERAGE ( Product[Margin %] )

Explanation: Take a hypothetical example with no discounts. Product A has a 20% margin and sold 1,000 units for 100,000 in sales and 80,000 in cost. Product B has a 50% margin and sold 10 units for 10,000 in sales and 5,000 in cost. AVERAGE of the column gives 35%, and SUM gives 70%, which is not a margin at all. The explicit [Profit Margin %] measure divides total profit (25,000) by total sales (110,000) and returns about 22.7%, which reflects what actually sold. Always use the measure for ratios.

Calculated Tables (13-18)

Example 13: Date Table

Scenario: A model has no date dimension, and time intelligence needs one. New calculated table named 'Date'. (The sample model already has one. This shows how you would build it if it did not.)

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "MonthNumber", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "mmmm" ),
    "MonthYearSort", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
    "FiscalYear", YEAR ( EDATE ( [Date], 6 ) )
)

Explanation: CALENDAR returns one row per day between two dates, and ADDCOLUMNS adds attributes. The fiscal year here assumes a June 30 year-end, matching the Sales Fiscal YTD measure. After creating it, mark it as a date table on [Date] and set sort-by-column properties. This is a short version, and my Date table article covers the full one.

Example 14: Role-Playing Ship Date Table

Scenario: Analysts want to slice by order month and ship month side by side. New calculated table named 'Ship Date'.

'Ship Date' = 'Date'

Explanation: This creates a copy of the Date table. It needs its own new relationship, 'Ship Date'[Date] to Sales[ShipDate], which is an addition to the sample model. Properties such as sort-by-column and date-table marking may need to be set again on the copy, so check it. Choose this over USERELATIONSHIP when both dates must appear in the same visual or slicers. Choose USERELATIONSHIP when you want a smaller model and need one date at a time.

Example 15: Metric Selector Table

Scenario: Users want a slicer to switch one chart between Sales, Profit and Quantity. New calculated table named 'Metric Selector', plus one new measure.

'Metric Selector' =
DATATABLE (
    "Metric", STRING,
    "MetricOrder", INTEGER,
    {
        { "Sales", 1 },
        { "Profit", 2 },
        { "Quantity", 3 }
    }
)

Selected Metric Value =
SWITCH (
    SELECTEDVALUE ( 'Metric Selector'[Metric], "Sales" ),
    "Sales", [Total Sales],
    "Profit", [Total Profit],
    "Quantity", [Total Quantity],
    BLANK ()
)

Explanation: The table is disconnected: it has no relationships, so selecting a value filters nothing in the model. The measure reads the selection with SELECTEDVALUE and returns the matching base measure. Sort Metric by MetricOrder so the slicer keeps its order. One number format applies to the measure, so mixing currency and quantity can look odd. Power BI also offers field parameters for this purpose, so check the current documentation.

Example 16: Discount What-If Table

Scenario: Sales planners want to test "what if every line had this discount?". New calculated table named 'Discount Scenario', plus one new measure.

'Discount Scenario' =
SELECTCOLUMNS (
    GENERATESERIES ( 0, 30, 5 ),
    "Discount %", DIVIDE ( [Value], 100 )
)

Sales at Scenario Discount =
VAR SelectedDiscount = SELECTEDVALUE ( 'Discount Scenario'[Discount %], 0 )
RETURN
    SUMX (
        'Sales Order Details',
        'Sales Order Details'[Quantity]
            * 'Sales Order Details'[UnitPrice]
            * ( 1 - SelectedDiscount )
    )

Explanation: GENERATESERIES returns a column called Value with 0, 5, 10 and so on up to 30. I generate whole numbers and divide by 100 to avoid decimal drift. The measure replaces each line's discount with the selected one, and returns the undiscounted total if nothing is selected. Like Example 15, the table is disconnected, and it is a good fit for scenario slicers.

Example 17: Top 10 Customers Table

Scenario: An executive wants a fixed list of the ten biggest customers. New calculated table named 'Top 10 Customers'.

'Top 10 Customers' =
SELECTCOLUMNS (
    TOPN ( 10, Customer, [Total Sales], DESC ),
    "CustomerKey", Customer[CustomerKey],
    "CustomerName", Customer[CustomerName],
    "Lifetime Sales", [Total Sales]
)

Explanation: TOPN ranks customers by [Total Sales] and keeps the top ten, and ties can return more than ten rows. The ranking is computed at refresh with no report filters, so it is static until the next refresh. It will not change when a user picks a year. If you need a ranking that responds to slicers, build it with a measure instead.

Example 18: Sales Summary Table

Scenario: A heavy report repeatedly shows sales by category and year. New calculated table named 'Sales Summary'.

'Sales Summary' =
SUMMARIZECOLUMNS (
    Product[Category],
    'Date'[Year],
    "Sales", [Total Sales],
    "Profit", [Total Profit]
)

Explanation: SUMMARIZECOLUMNS groups by Category and Year and calculates both measures for each pair. It leaves out combinations where every measure is blank. A summary table is worth it only when it removes a proven bottleneck or feeds a specific need, because it adds refresh work and memory and is static between refreshes. Test the report without it first.

Advanced: Decisions and Governance (19-20)

Example 19: Order Size Band Three Ways

Scenario: The business wants to classify orders as Small, Medium or Large. The thresholds are illustrative.

Option A: calculated column in the Sales table:

Order Size Band =
VAR OrderTotal = CALCULATE ( [Total Sales] )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( OrderTotal ), "No Lines",
        OrderTotal < 100, "Small",
        OrderTotal < 500, "Medium",
        "Large"
    )

Option B: Power Query (M), which assumes queries named Sales and Sales Order Details:

let
    OrderTotals =
        Table.Group (
            #"Sales Order Details",
            { "SalesOrderID" },
            { { "OrderTotal", each List.Sum ( [SalesAmount] ), type number } }
        ),
    Merged =
        Table.NestedJoin ( Sales, { "SalesOrderID" }, OrderTotals, { "SalesOrderID" }, "Totals", JoinKind.LeftOuter ),
    Expanded =
        Table.ExpandTableColumn ( Merged, "Totals", { "OrderTotal" } ),
    Banded =
        Table.AddColumn (
            Expanded,
            "Order Size Band",
            each
                if [OrderTotal] = null then "No Lines"
                else if [OrderTotal] < 100 then "Small"
                else if [OrderTotal] < 500 then "Medium"
                else "Large",
            type text
        ),
    Result = Table.RemoveColumns ( Banded, { "OrderTotal" } )
in
    Result

Option C: measure, counting orders in one band:

Small Orders =
CALCULATE (
    [Order Count],
    FILTER ( Sales, [Total Sales] < 100 )
)

Explanation: Choose the measure when you only need counts or totals by band, because it reacts to slicers and stores nothing. Choose a stored column when the band must be a slicer or axis. When you have a choice, build that column in Power Query or the source, since the logic runs before the model. Use the calculated column when the rule needs model relationships or measures, or when you cannot change the pipeline.

Example 20: Model Clean-Up Checklist

Scenario: You inherit a model with dozens of calculated columns and tables. Review each one with this list.

For every calculated column or table, ask:
[ ] Could this be a measure?
[ ] Could this move to the source or Power Query?
[ ] Is it high-cardinality (many distinct values)?
[ ] Is it used in any visual, relationship, sort or measure?
[ ] Is it a ratio or average stored per row?
[ ] Does it use TODAY () or NOW () and assume live updates?
[ ] Does it depend on a long chain of other calculated objects?
[ ] Should it be hidden from report authors?
[ ] Is it documented (purpose, owner, dependencies)?
[ ] Is the same logic duplicated elsewhere?

Explanation: Work through the list column by column. Any "yes" to the first two questions is a candidate for removal or relocation. Check usage before deleting, because reports and other tools may depend on a column. Run this review regularly, and tools such as Tabular Editor can help with dependencies.

Common Mistakes and Best Practices

• Building ratios as columns. Summing or averaging a stored ratio gives wrong totals. Use a measure.
• Creating a column when a measure would do. If the result should react to slicers, it belongs in a measure.
• Ignoring high cardinality. Columns with almost unique values compress poorly and add memory.
• Using TODAY () in a stored column. The value is fixed at refresh, not live.
• Creating circular dependencies. Two objects that depend on each other cause an error. Keep dependency chains short.
• Building calculated tables that could be source tables. If a table can come from the source or Power Query, load it from there.
• Forgetting to hide helper columns. Hide anything report authors should not use.
• Not documenting calculated objects. Record the purpose, owner and dependencies for each one.

Frequently Asked Questions

Are calculated columns slower than measures?

Not necessarily. A calculated column does its work at refresh and stores the result, while a measure does its work at query time. Which is better depends on your model, so test rather than assume.

Do calculated columns update when I change a slicer?

No. Their values are fixed at refresh. You can slice by a calculated column, but its stored values do not change with your selection. Use a measure for anything that must react.

Can a calculated table have relationships?

Yes. Its columns can take part in relationships like those of any other table. Examples 14 and 17 show cases where you might add one. The table is still static until refresh.

Should I use Power Query instead?

Often, yes, for row-level shaping. Power Query and the source run before the model and keep DAX lean. Use a calculated column or table when the logic needs model relationships, measures, or DAX-only constructs.

Conclusion: Key Takeaways

• A calculated column is stored row by row; a calculated table is a stored table; a measure is calculated at query time.
• Use calculated objects for slicers, axes, bands, keys, sort columns and tables with no source.
• Prefer, in order: the source or Power Query, then a measure, then a calculated column or table.
• Never store ratios per row and expect correct totals.
• Cost and speed depend on your model, so review calculated objects regularly.

Related reading: explicit measures, the Date table, CALCULATE, and row context vs filter context. Find them on exceltobi.com.

Ready to build cleaner Power BI and SSAS Tabular models? Explore more guides on exceltobi.com and put these patterns to work in your own model.