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.