DAX Basket Analysis Patterns: Building Market Basket, Product Affinity, and Cross-Sell Measures with INTERSECT and EXCEPT

Introduction

Imagine you're a data analyst for a mid-size grocery retailer. Your merchandising team wants to know: when customers buy olive oil, what else ends up in their basket? Which product combinations appear together far more often than chance would predict? And when a customer has already purchased a stand mixer, what related products should the website recommend to them next? These questions all belong to the same analytical family — basket analysis — and they're genuinely hard to answer in SQL, let alone in a tool built around a star schema.

DAX, however, has a set of underused table functions that make basket analysis surprisingly tractable once you understand the underlying mechanics. INTERSECT and EXCEPT are not glamorous. You won't find them in blog posts about fancy visuals. But in the hands of someone who understands filter context and virtual table construction, they become the foundation for market basket measures that would make an ML engineer raise an eyebrow. By the end of this lesson, you'll be able to build co-occurrence counts, lift scores, affinity percentages, and cross-sell gap measures — all as DAX measures that respond dynamically to slicers.

What you'll learn:

• How INTERSECT and EXCEPT operate on table expressions, and why that matters for basket logic
• How to construct a co-occurrence measure that counts transactions containing two products simultaneously
• How to compute support, confidence, and lift — the three pillars of association rule mining — entirely in DAX
• How to build a dynamic cross-sell gap measure that identifies which customers own product A but not product B
• Performance implications of basket analysis measures and how to mitigate them in large models

---

Prerequisites

This is an expert-level lesson. You should be comfortable with:

• Writing multi-step DAX using VAR and RETURN — if you need a refresher, see DAX Variables in Practice: Using VAR and RETURN to Simplify Complex Measures
• How CALCULATE modifies filter context — the mechanics are covered thoroughly in Understanding DAX: CALCULATE and Filter Context
• Table functions like FILTER, VALUES, and ALL — see DAX Table Functions Explained: FILTER, ALL, ALLEXCEPT, and VALUES in Practice
• A working knowledge of how relationships propagate filters in Power BI — DAX Relationships 101: How Power BI Filters Flow Across Tables and Why It Matters for Your Measures covers this well

---

The Data Model You'll Be Working With

Before writing a single line of DAX, you need a model that supports basket analysis. The minimum viable model has these tables:

Orders — one row per order line:

• OrderID (text or integer)
• CustomerID
• ProductID
• Quantity
• Revenue
• OrderDate

Products — one row per product:

• ProductID
• ProductName
• Category
• SubCategory

Customers — one row per customer:

• CustomerID
• CustomerName
• Segment

The standard star schema has Products and Customers as dimension tables connected to Orders as the fact table. This is exactly what you want — filters on Products flow into Orders via the relationship, which is the mechanism basket analysis exploits.

The key insight about basket analysis in this model is that "basket" is represented implicitly by OrderID. Two products share a basket if and only if they appear in the same OrderID. Your job is to teach DAX to express that idea.

---

Understanding INTERSECT and EXCEPT

Let's start with the primitives before we build anything complex.

INTERSECT(table1, table2) returns all rows from table1 that also exist in table2. The tables must have the same number of columns, and matching is done by position (column names don't matter, only data types and order). Duplicates are preserved from the left table.

EXCEPT(table1, table2) returns all rows from table1 that do not exist in table2. Same structural requirements apply.

Both functions operate on full row values — they're doing set operations on tuples, not just single columns. This is what makes them powerful for basket analysis.

Here's a concrete example of what INTERSECT does in basket logic. Suppose you want all OrderIDs that contain Product A:

// Orders containing Product A
VAR OrdersWithProductA =
    SELECTCOLUMNS(
        FILTER(Orders, Orders[ProductID] = "PROD-A"),
        "OrderID", Orders[OrderID]
    )

And all OrderIDs that contain Product B:

// Orders containing Product B
VAR OrdersWithProductB =
    SELECTCOLUMNS(
        FILTER(Orders, Orders[ProductID] = "PROD-B"),
        "OrderID", Orders[OrderID]
    )

Now INTERSECT(OrdersWithProductA, OrdersWithProductB) gives you every OrderID that contained both. That's co-occurrence — the mathematical heart of market basket analysis.

The corrected pattern uses DISTINCT:

VAR OrdersWithProductA =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(Orders, Orders[ProductID] = "PROD-A"),
            "OrderID", Orders[OrderID]
        )
    )

This small discipline prevents a class of subtle bugs that are genuinely hard to diagnose once they're embedded in production measures.

---

Building the Co-Occurrence Count

Co-occurrence count is the simplest basket metric: how many transactions (or customers) contain both the selected product and each other product in the catalogue? This is the foundation everything else builds on.

The setup uses a matrix visual with a product slicer on one axis (let's call it the "anchor" product) and a product list on rows. But there's a problem: if both the row context and the slicer filter the same Products table, they'll step on each other. You need two separate filters on the same table.

The standard solution is to use a disconnected "anchor" product table that doesn't participate in any model relationship. This table drives the slicer, while the main Products table drives the matrix rows.

Step 1: Create the anchor table.

In Power Query or using a DAX calculated table:

AnchorProducts =
SELECTCOLUMNS(
    'Products',
    "AnchorProductID", 'Products'[ProductID],
    "AnchorProductName", 'Products'[ProductName]
)

This table is not connected to anything in the model. It exists purely to capture user selection without polluting the filter context on Products.

Step 2: Write the co-occurrence measure.

Co-Occurrence Count =
VAR SelectedAnchorID =
    SELECTEDVALUE( AnchorProducts[AnchorProductID] )

VAR OrdersWithAnchor =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( ALL( Orders ), Orders[ProductID] = SelectedAnchorID ),
            "OrderID", Orders[OrderID]
        )
    )

VAR OrdersWithCurrentProduct =
    DISTINCT(
        SELECTCOLUMNS(
            Orders,  -- This picks up the current row filter from the visual
            "OrderID", Orders[OrderID]
        )
    )

VAR CoOccurringOrders =
    INTERSECT( OrdersWithAnchor, OrdersWithCurrentProduct )

RETURN
    COUNTROWS( CoOccurringOrders )

Let's unpack what's happening here. SelectedAnchorID captures the value from the disconnected slicer — the product the user has nominated as their anchor. OrdersWithAnchor uses ALL(Orders) to bypass any current filter context and retrieve orders containing that specific product across all data. OrdersWithCurrentProduct intentionally does not use ALL() — it respects the current filter context, which in a matrix visual will be one product per row. INTERSECT then finds the overlap, and COUNTROWS gives you the basket co-occurrence count.

When this measure is placed in a matrix with Products[ProductName] on rows and the anchor slicer set to "Extra Virgin Olive Oil," you'll see how many transactions contained each other product alongside olive oil. High numbers at the top, zeros (or near-zeros) at the bottom.

---

Computing Support, Confidence, and Lift

Co-occurrence counts are useful for absolute volume, but they're biased by popularity. A product that appears in 90% of all transactions will co-occur frequently with everything — that's not an insight, that's just prevalence. Association rule mining addresses this with three normalized metrics.

Support = proportion of all transactions containing the product combination
Confidence = given a basket contains the anchor product, what fraction also contains the target?
Lift = confidence divided by the baseline probability of the target product appearing at all

A lift above 1.0 means the two products appear together more often than would be expected by chance. Lift = 2.5 means the combination is 2.5x more likely than random. This is the metric your merchandising team will actually care about.

Total Transaction Count

First, build a reusable baseline measure:

Total Distinct Orders =
COUNTROWS(
    DISTINCT(
        SELECTCOLUMNS( ALL( Orders ), "OrderID", Orders[OrderID] )
    )
)

Support

Basket Support =
VAR SelectedAnchorID =
    SELECTEDVALUE( AnchorProducts[AnchorProductID] )

VAR OrdersWithAnchor =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( ALL( Orders ), Orders[ProductID] = SelectedAnchorID ),
            "OrderID", Orders[OrderID]
        )
    )

VAR OrdersWithCurrentProduct =
    DISTINCT(
        SELECTCOLUMNS( Orders, "OrderID", Orders[OrderID] )
    )

VAR CoOccurringOrders = INTERSECT( OrdersWithAnchor, OrdersWithCurrentProduct )

VAR TotalOrders = [Total Distinct Orders]

RETURN
    DIVIDE( COUNTROWS( CoOccurringOrders ), TotalOrders )

Support is symmetric — it doesn't matter which product is the anchor and which is the target. This is the frequency of the combination across all transactions.

Confidence

Confidence is asymmetric. "If olive oil, then pasta" has a different confidence than "if pasta, then olive oil."

Basket Confidence =
VAR SelectedAnchorID =
    SELECTEDVALUE( AnchorProducts[AnchorProductID] )

VAR OrdersWithAnchor =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( ALL( Orders ), Orders[ProductID] = SelectedAnchorID ),
            "OrderID", Orders[OrderID]
        )
    )

VAR OrdersWithCurrentProduct =
    DISTINCT(
        SELECTCOLUMNS( Orders, "OrderID", Orders[OrderID] )
    )

VAR CoOccurringOrders = INTERSECT( OrdersWithAnchor, OrdersWithCurrentProduct )

VAR AnchorOrderCount = COUNTROWS( OrdersWithAnchor )

RETURN
    DIVIDE( COUNTROWS( CoOccurringOrders ), AnchorOrderCount )

Notice the denominator has changed from TotalOrders to AnchorOrderCount. You're now asking: of all transactions that contained the anchor product, what fraction also contained the current product? This is "confidence" in the association rule literature.

Lift

Basket Lift =
VAR SelectedAnchorID =
    SELECTEDVALUE( AnchorProducts[AnchorProductID] )

VAR OrdersWithAnchor =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( ALL( Orders ), Orders[ProductID] = SelectedAnchorID ),
            "OrderID", Orders[OrderID]
        )
    )

VAR OrdersWithCurrentProduct =
    DISTINCT(
        SELECTCOLUMNS( Orders, "OrderID", Orders[OrderID] )
    )

VAR CoOccurringOrders = INTERSECT( OrdersWithAnchor, OrdersWithCurrentProduct )

VAR AnchorOrderCount = COUNTROWS( OrdersWithAnchor )

VAR TargetOrderCount =
    COUNTROWS( OrdersWithCurrentProduct )

VAR TotalOrders = [Total Distinct Orders]

VAR Confidence = DIVIDE( COUNTROWS( CoOccurringOrders ), AnchorOrderCount )

VAR TargetBaseline = DIVIDE( TargetOrderCount, TotalOrders )

RETURN
    DIVIDE( Confidence, TargetBaseline )

Lift above 1 indicates positive association. Lift of exactly 1 means the products are statistically independent. Lift below 1 means they tend to appear in different baskets — potentially useful for anti-affinity analysis (products that compete with each other, perhaps).

---

The EXCEPT Pattern: Cross-Sell Gap Analysis

Co-occurrence and lift tell you about what customers do buy together. EXCEPT lets you answer the complementary question: which customers own Product A but not Product B? This is the cross-sell gap — the population your sales team should be targeting.

The pattern here works at customer level rather than order level, because for cross-sell purposes you typically care about the customer's complete purchase history, not individual transactions.

Cross-Sell Gap Customers =
VAR SelectedAnchorID =
    SELECTEDVALUE( AnchorProducts[AnchorProductID] )

VAR CustomersWhoHaveAnchor =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( ALL( Orders ), Orders[ProductID] = SelectedAnchorID ),
            "CustomerID", Orders[CustomerID]
        )
    )

VAR CustomersWhoHaveCurrentProduct =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( ALL( Orders ), Orders[ProductID] IN VALUES( Orders[ProductID] ) ),
            "CustomerID", Orders[CustomerID]
        )
    )

-- Customers who have the anchor but NOT the current product
VAR GapCustomers =
    EXCEPT( CustomersWhoHaveAnchor, CustomersWhoHaveCurrentProduct )

RETURN
    COUNTROWS( GapCustomers )

Wait — that CustomersWhoHaveCurrentProduct definition is too broad. Let me show you the corrected version that properly scopes to the product currently in filter context:

Cross-Sell Gap Customers =
VAR SelectedAnchorID =
    SELECTEDVALUE( AnchorProducts[AnchorProductID] )

-- Customers who have ever purchased the anchor product
VAR CustomersWhoHaveAnchor =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( ALL( Orders ), Orders[ProductID] = SelectedAnchorID ),
            "CustomerID", Orders[CustomerID]
        )
    )

-- Customers who have ever purchased the product in the current row context
VAR CurrentProductID =
    SELECTEDVALUE( Products[ProductID] )

VAR CustomersWhoHaveCurrentProduct =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( ALL( Orders ), Orders[ProductID] = CurrentProductID ),
            "CustomerID", Orders[CustomerID]
        )
    )

-- The gap: has anchor, does NOT have current product
VAR GapCustomers =
    EXCEPT( CustomersWhoHaveAnchor, CustomersWhoHaveCurrentProduct )

RETURN
    COUNTROWS( GapCustomers )

Here's what this tells you: if you've selected "Stand Mixer" as your anchor product and you're looking at the row for "Bread Proofing Basket," the measure returns the count of customers who bought a stand mixer but have never purchased a proofing basket. That's your targeted cross-sell audience — people who already signalled high baking intent and are likely receptive to the complementary product.

You can express this as a percentage of the anchor's customer base to get a cross-sell penetration rate:

Cross-Sell Gap % =
VAR SelectedAnchorID =
    SELECTEDVALUE( AnchorProducts[AnchorProductID] )

VAR CustomersWhoHaveAnchor =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( ALL( Orders ), Orders[ProductID] = SelectedAnchorID ),
            "CustomerID", Orders[CustomerID]
        )
    )

VAR CurrentProductID =
    SELECTEDVALUE( Products[ProductID] )

VAR CustomersWhoHaveCurrentProduct =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( ALL( Orders ), Orders[ProductID] = CurrentProductID ),
            "CustomerID", Orders[CustomerID]
        )
    )

VAR GapCustomers =
    EXCEPT( CustomersWhoHaveAnchor, CustomersWhoHaveCurrentProduct )

RETURN
    DIVIDE(
        COUNTROWS( GapCustomers ),
        COUNTROWS( CustomersWhoHaveAnchor )
    )

A cross-sell gap of 78% on "Bread Proofing Basket" when anchored on "Stand Mixer" means 78% of stand mixer owners have never bought the basket — a massive untapped opportunity.

---

Building a Complete Affinity Score Matrix

Let me now show you how to combine these measures into a presentation-ready affinity score that's suitable for a recommendation engine or category management dashboard.

The affinity score I find most useful in practice is a weighted composite: 40% lift, 40% confidence, 20% support-normalized volume. This gives you a single number that balances statistical strength (lift), directional reliability (confidence), and absolute business scale (support). You can adjust weights based on what your business cares about.

Affinity Score =
VAR SelectedAnchorID =
    SELECTEDVALUE( AnchorProducts[AnchorProductID] )

VAR CurrentProductID =
    SELECTEDVALUE( Products[ProductID] )

-- Return blank if same product or no selection
VAR IsSameProduct = IF( SelectedAnchorID = CurrentProductID, TRUE(), FALSE() )
VAR HasSelection = NOT ISBLANK( SelectedAnchorID )

VAR OrdersWithAnchor =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( ALL( Orders ), Orders[ProductID] = SelectedAnchorID ),
            "OrderID", Orders[OrderID]
        )
    )

VAR OrdersWithCurrentProduct =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( ALL( Orders ), Orders[ProductID] = CurrentProductID ),
            "OrderID", Orders[OrderID]
        )
    )

VAR CoOccurringOrders = INTERSECT( OrdersWithAnchor, OrdersWithCurrentProduct )
VAR CoCount = COUNTROWS( CoOccurringOrders )
VAR AnchorCount = COUNTROWS( OrdersWithAnchor )
VAR TargetCount = COUNTROWS( OrdersWithCurrentProduct )
VAR TotalOrders = [Total Distinct Orders]

VAR Confidence = DIVIDE( CoCount, AnchorCount )
VAR TargetBaseline = DIVIDE( TargetCount, TotalOrders )
VAR Lift = DIVIDE( Confidence, TargetBaseline )
VAR Support = DIVIDE( CoCount, TotalOrders )

-- Normalized support: scale against max possible support
-- (which is min of anchor frequency and target frequency)
VAR MaxPossibleSupport =
    DIVIDE( MIN( AnchorCount, TargetCount ), TotalOrders )

VAR NormalizedSupport = DIVIDE( Support, MaxPossibleSupport )

-- Weighted composite (adjust weights to taste)
VAR WeightedScore =
    ( 0.40 * DIVIDE( Lift, 5 ) )        -- Lift normalized to roughly 0-1 range assuming max lift ~5
    + ( 0.40 * Confidence )
    + ( 0.20 * NormalizedSupport )

RETURN
    IF(
        HasSelection && NOT IsSameProduct,
        ROUND( WeightedScore * 100, 1 ),  -- Return as 0-100 score
        BLANK()
    )

This affinity score, sorted descending in a table visual, gives you an instant ranked list of "products most associated with this anchor product." That's directly actionable for category managers, email campaign targeting, and website recommendation placement.

---

Advanced Pattern: Category-Level Basket Analysis

Individual product affinity matrices are useful, but with large catalogues they become unwieldy. Category-level basket analysis asks: which product categories tend to appear in the same basket? This helps buyers understand cross-department purchase behaviour.

The pattern is nearly identical, but you collapse products to categories before computing intersections:

Category Co-Occurrence =
VAR SelectedAnchorCategory =
    SELECTEDVALUE( AnchorProducts[AnchorCategory] )  -- Add category to anchor table

VAR OrdersWithAnchorCategory =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                ALL( Orders ),
                RELATED( Products[Category] ) = SelectedAnchorCategory
            ),
            "OrderID", Orders[OrderID]
        )
    )

VAR CurrentCategory =
    SELECTEDVALUE( Products[Category] )

VAR OrdersWithCurrentCategory =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                ALL( Orders ),
                RELATED( Products[Category] ) = CurrentCategory
            ),
            "OrderID", Orders[OrderID]
        )
    )

VAR SharedOrders = INTERSECT( OrdersWithAnchorCategory, OrdersWithCurrentCategory )

RETURN
    COUNTROWS( SharedOrders )

---

Using INTERSECT Across Customer Segments

INTERSECT also enables a different analytical question: customer segment overlap. Which customers purchased in both Category A and Category B? This is useful for understanding multi-category shoppers and identifying your most engaged buyers.

Multi-Category Shopper Count =
VAR Cat1 = "Bakery"
VAR Cat2 = "Deli"

VAR CustomersBoughtCat1 =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                ALL( Orders ),
                RELATED( Products[Category] ) = Cat1
            ),
            "CustomerID", Orders[CustomerID]
        )
    )

VAR CustomersBoughtCat2 =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                ALL( Orders ),
                RELATED( Products[Category] ) = Cat2
            ),
            "CustomerID", Orders[CustomerID]
        )
    )

RETURN
    COUNTROWS( INTERSECT( CustomersBoughtCat1, CustomersBoughtCat2 ) )

For a dynamic version driven by a multi-select slicer, you'd capture VALUES(SelectedCategories[Category]) and iterate — but that pattern requires either GENERATE or SUMMARIZE and grows in complexity quickly. The DAX Virtual Tables in Practice: Using ADDCOLUMNS, SUMMARIZE, and GENERATEALL to Build Complex Aggregations Without Helper Tables lesson covers the virtual table machinery you'd need for that extension.

---

Performance Considerations and Optimization

Here's where we have to be honest with you: basket analysis measures are expensive. Every measure call involves constructing virtual tables in memory, then performing set operations on them. In a matrix visual with 200 products on rows and the measure evaluated for each cell, that's 200 separate INTERSECT computations per render cycle. With 5 million order rows, this can grind to a halt.

Mitigation strategy 1: Pre-aggregate to an order-product bridge table

The most effective optimization is to shift work from query time to refresh time. Instead of filtering raw Orders at query time, pre-compute a table of (OrderID, ProductID) pairs with duplicates removed. In Power Query:

= Table.Distinct(
    Table.SelectColumns(Orders, {"OrderID", "ProductID"})
)

Load this as OrderProductBridge. Your basket measures now filter a smaller, already-deduplicated table instead of the full fact table. On a typical retail dataset this can reduce measure evaluation time by 60-80%.

Mitigation strategy 2: Limit the product list

Add a minimum support filter to your visual. Use a slicer or measure-based filter to exclude products that appear in fewer than N transactions before the basket analysis runs. This reduces the number of cells the visual needs to evaluate.

Basket Lift (Filtered) =
VAR MinimumSupport = 50  -- Require at least 50 appearances

VAR CurrentProductOrderCount =
    COUNTROWS(
        DISTINCT(
            SELECTCOLUMNS( Orders, "OrderID", Orders[OrderID] )
        )
    )

RETURN
    IF(
        CurrentProductOrderCount >= MinimumSupport,
        [Basket Lift],
        BLANK()
    )

Mitigation strategy 3: Use aggregation tables

If your model serves a published report with high concurrency, consider building a pre-computed co-occurrence table in Power Query that contains every product pair and their co-occurrence counts. Your DAX measures then become simple lookups rather than set operations. You lose the dynamic anchor-selection capability, but for fixed-parameter reports the trade-off is often worthwhile.

For deep performance profiling of your basket measures, Performance Tuning DAX: Optimize Slow Measures with DAX Studio walks you through the storage engine vs formula engine breakdown and how to read the VertiPaq query plan for INTERSECT-based measures.

---

Dynamic Segmentation Applied to Basket Analysis

One powerful extension is combining basket analysis with dynamic customer segmentation. Rather than asking "which products co-occur for all customers," you ask "which products co-occur specifically for high-value customers, or for customers in the Northeast, or for customers who joined in the last 12 months?"

Because your basket measures respect filter context on the Customers table (via the Orders → Customers relationship), standard report slicers on customer attributes will automatically scope the analysis. But you can also bake segmentation directly into the measure.

High-Value Customer Basket Lift =
VAR SelectedAnchorID =
    SELECTEDVALUE( AnchorProducts[AnchorProductID] )

-- Define high-value customers inline (or reference a segment measure)
VAR HighValueCustomers =
    FILTER(
        ALL( Customers ),
        Customers[LifetimeRevenue] > 1000
    )

-- Orders placed by high-value customers only
VAR HVOrders =
    FILTER(
        ALL( Orders ),
        Orders[CustomerID] IN SELECTCOLUMNS( HighValueCustomers, "CustomerID", Customers[CustomerID] )
    )

VAR OrdersWithAnchor =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( HVOrders, Orders[ProductID] = SelectedAnchorID ),
            "OrderID", Orders[OrderID]
        )
    )

VAR CurrentProductID =
    SELECTEDVALUE( Products[ProductID] )

VAR OrdersWithCurrentProduct =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( HVOrders, Orders[ProductID] = CurrentProductID ),
            "OrderID", Orders[OrderID]
        )
    )

VAR CoCount = COUNTROWS( INTERSECT( OrdersWithAnchor, OrdersWithCurrentProduct ) )
VAR AnchorCount = COUNTROWS( OrdersWithAnchor )
VAR TargetCount = COUNTROWS( OrdersWithCurrentProduct )

VAR TotalHVOrders =
    COUNTROWS( DISTINCT( SELECTCOLUMNS( HVOrders, "OrderID", Orders[OrderID] ) ) )

VAR Confidence = DIVIDE( CoCount, AnchorCount )
VAR TargetBaseline = DIVIDE( TargetCount, TotalHVOrders )

RETURN
    DIVIDE( Confidence, TargetBaseline )

This pattern pairs beautifully with the segmentation techniques in Dynamic Segmentation and Grouping with DAX: Build Flexible Customer Analytics, where you'll find approaches for making the segment definition itself dynamic rather than hardcoded.

---

Hands-On Exercise

Work through these exercises using a sales dataset that has at least 10,000 order lines, 50+ products, and 5+ categories. Retail demo datasets from Kaggle (such as the Online Retail II dataset) work well.

Exercise 1: Build the disconnected anchor table

1. Create the AnchorProducts calculated table using SELECTCOLUMNS on your Products table
2. Confirm it has no relationships in Model view
3. Add it to a slicer and verify the slicer has no effect on other visuals (it shouldn't, because it's disconnected)

Exercise 2: Build and validate the co-occurrence measure

1. Write the Co-Occurrence Count measure
2. Place it in a matrix with Products[ProductName] on rows
3. Select a high-volume product as your anchor (choose one that appears in at least 10% of orders)
4. Verify that when the anchor product appears as a row, it shows the total number of orders containing that product (perfect self-co-occurrence)
5. Verify that the total across all rows exceeds the co-occurrence count for the anchor itself (because the total aggregates differently)

Exercise 3: Build support, confidence, and lift

1. Implement all three measures
2. Add them to the same matrix
3. Sort by Lift descending
4. Find the top-5 product associations by lift
5. Now sort by Confidence descending — does the ranking change significantly? Write down why the difference matters

Exercise 4: Cross-sell gap

1. Implement Cross-Sell Gap Customers and Cross-Sell Gap %
2. Identify your top cross-sell opportunity: which anchor/target combination has the highest lift AND the highest gap percentage simultaneously?
3. Build a measure called Cross-Sell Priority Score that multiplies Basket Lift by Cross-Sell Gap % — this surfaces the combinations that are both strongly associated and widely untapped

Exercise 5: Category-level analysis

1. Add a Category column to your AnchorProducts table
2. Build the category co-occurrence measure
3. Create a matrix with categories on both rows and columns (use a copy of your category dimension as a disconnected anchor dimension)
4. Identify the category pair with the highest lift

---

Common Mistakes & Troubleshooting

Mistake 1: Not using DISTINCT before INTERSECT

Symptom: Co-occurrence counts are higher than the actual number of transactions.
Cause: Duplicate OrderIDs in one or both input tables.
Fix: Wrap both inputs to INTERSECT in DISTINCT().

Mistake 2: Using the main Products table for both the anchor slicer and the row context

Symptom: Every product shows the same co-occurrence count (usually the full count for the anchor), or the measure ignores your slicer.
Cause: The anchor slicer is filtering the same table that the visual rows come from, creating a conflict.
Fix: Always use a disconnected anchor table. This is non-negotiable for basket analysis in Power BI.

Mistake 3: Forgetting ALL() around the fact table for anchor lookups

Symptom: The measure returns blank or shows the same value for every row regardless of anchor selection.
Cause: When computing OrdersWithAnchor, the current filter context from the visual's rows may already exclude the anchor product. Using ALL(Orders) ensures you see the full dataset for the anchor lookup.
Fix: Use FILTER(ALL(Orders), Orders[ProductID] = SelectedAnchorID) — the ALL() is not optional here.

Mistake 4: Measuring lift on infrequent products

Symptom: Lift values of 50, 100, or higher for certain product pairs.
Cause: When either product appears in very few transactions, the denominator in the baseline frequency becomes tiny, inflating lift astronomically.
Fix: Add a minimum support threshold — require at least N co-occurrences before displaying lift. Use conditional formatting or a measure wrapper that returns BLANK() below the threshold.

Mistake 5: Performance collapse in large models

Symptom: Matrix visual takes 30+ seconds to load, or times out entirely.
Cause: INTERSECT on large virtual tables is O(n) but with high constant factors when done 200+ times per visual render.
Fix: Pre-aggregate to a deduplicated order-product bridge table, limit product list to items above minimum support, and consider pre-computing co-occurrence counts in Power Query for fixed-report scenarios.

Mistake 6: SELECTEDVALUE returning BLANK for multi-select slicers

Symptom: All cells show BLANK when the user selects more than one anchor product.
Cause: SELECTEDVALUE returns BLANK when more than one value is selected.
Fix: Either restrict the anchor slicer to single-select (via slicer settings), or replace SELECTEDVALUE with MINX(AnchorProducts, AnchorProducts[AnchorProductID]) and accept that multiple selections will use the first anchor. For truly multi-anchor basket analysis, the pattern needs to use IN VALUES(...) which changes the semantics significantly.

---

Summary & Next Steps

You've built a complete basket analysis toolkit in DAX, grounded in the set-theory primitives INTERSECT and EXCEPT. Here's what you can now do:

• Compute co-occurrence counts between any two products using a disconnected anchor table pattern
• Calculate support, confidence, and lift to normalize raw counts into statistically meaningful association strengths
• Build cross-sell gap measures that quantify the untapped opportunity between product pairs at the customer level
• Compose a weighted affinity score suitable for ranking recommendations
• Apply basket analysis within customer segments for targeted analytics
• Understand the performance profile of these measures and when to pre-aggregate

The core pattern — build virtual single-column tables representing "baskets containing X" and "baskets containing Y," then apply set operations — is flexible enough to answer dozens of variants on the basket analysis question. Product substitution (anti-affinity via lift < 1), multi-product basket queries (chain INTERSECT calls), category migration analysis (using EXCEPT on time-split customer sets) — all follow from the same primitives you've practiced here.

For deeper context on how these measures interact with the Vertipaq engine and how to profile them in production, spend time with Performance Tuning DAX: Optimize Slow Measures with DAX Studio. For extending the patterns to customer lifetime value and cohort-level analysis — which pairs naturally with basket analysis in a complete customer analytics practice — DAX Cohort Analysis: Building Retention, Churn, and Lifetime Value Measures with GENERATE and Date-Based Segmentation is the natural continuation.

The best basket analysis measures are the ones that live in reports category managers and merchandisers actually open every week. Keep that end user in mind as you refine the visuals, and the DAX complexity underneath becomes entirely worth it.