Daily DAX : Day 405 MATCHBY

Power BI DAX - MATCHBY() Function

Introduced in: Power BI Desktop (March 2024 update) and later

What is MATCHBY?

MATCHBY is a new DAX function that explicitly defines how rows are matched when using row context in functions like EARLIER, RANKX, COUNTROWS with filter, etc.

It solves the long-standing ambiguity in DAX where the engine previously decided matching logic automatically (often causing bugs when tables have duplicate values).

Syntax

MATCHBY ( ,  [= | <= | >= | > | <] , ... )

Or more commonly used inside calculation functions:

RANKX ( 
    MATCHBY ( Customers, Customers[CustomerKey] ), 
    [Total Sales], 
    , DESC 
)

Key Use Cases

Use Case	Description	Example
Ranking with duplicate keys	Ensure correct ranking when multiple tables have same column names or duplicates	Rank sales by customer even if CustomerName is not unique
Complex EARLIER scenarios	Replace confusing EARLIER() with clear matching logic	Find previous order date for the same customer
Window functions (new DAX)	Required for OFFSET(), INDEX(), etc.	Moving averages, YoY growth with proper row alignment

Practical Example: Rank Sales by Customer (Safe from Duplicates)

// Old way (risky if CustomerName is not unique)
Customer Rank = 
RANKX (
    ALL ( Customers ),
    CALCULATE ( SUM ( Sales[Amount] ) ),
    , DESC, Dense
)

// New safe way using MATCHBY
Customer Rank = 
RANKX (
    MATCHBY ( Customers, Customers[CustomerKey] ),
    CALCULATE ( SUM ( Sales[Amount] ) ),
    , DESC, Dense
)

Why this matters: If two customers have the same name but different CustomerKey, the old version might rank them incorrectly. MATCHBY forces matching on the unique key.

With Window Functions (OFFSET / INDEX)

Previous Month Sales = 
CALCULATE (
    SUM ( Sales[Amount] ),
    OFFSET (
        -1,
        MATCHBY ( Dates, Dates[YearMonth] ),   // Match rows by Year-Month
        ORDERBY ( Dates[Date] )
    )
)

Summary

• MATCHBY = explicit row matching
• Replaces ambiguous default behavior
• Required for new window functions
• Makes DAX code more predictable and maintainable
• Best practice: Always use MATCHBY in RANKX, TOPN, OFFSET, etc. when possible

As of 2025, Microsoft recommends using MATCHBY in all new DAX measures involving row context.