How is Course X performing compared to my benchmark course over time?

 

The challenge lies in managing filter context, including which courses and filters to consider or ignore. A useful DAX pattern compares the current selection to a fixed reference while allowing other filters to operate normally.

Traditionally, we address this issue by using a separate table along with functions such as REMOVEFILTERS and SELECTEDVALUE. With DAX User-Defined Functions (UDFs), we can encapsulate this logic for reuse, making the approach more efficient.

 

The business problem: Compare everything to one fixed thing

 

Create a reporting model to compare a selected course (Course 2) with a benchmark course (Course 1) across metrics like enrollments, distinct users, and completion rates. The report should allow filtering by month, category, user type, and more, while keeping the reference course fixed. A disconnected reference table will enhance this functionality.

 

Enter DAX User-Defined Functions

DAX UDFs allow us to define reusable DAX logic as named functions with parameters. Microsoft describes UDFs as reusable DAX functions that can be used from measures, calculated columns, visual calculations, and even other UDFs. They support typed parameters such as ColumnRef, MeasureRef, TableRef, and CalendarRef.

 

That is a significant shift in how we can structure the logic of semantic models.

Collapse the reference-comparison pattern into one UDF

Here is the reusable pattern:

 

DEFINE
  FUNCTION IncrementalVsReference = ( BaseMeasure: MeasureRef, RefColumn: ColumnRef, RefValue: STRING ) =>
VAR SelectedVal = BaseMeasure
VAR RefVal =
    CALCULATE (
        BaseMeasure,
        REMOVEFILTERS ( RefColumn ),
        RefColumn = RefValue
    )
RETURN
    SelectedVal - RefVal

 

 

The function recognizes only the parameters: BaseMeasure, RefColumn, and RefValue. Let's break it down:

 

1. SelectedVal = BaseMeasure: This evaluates the measure within the current filter context.

2. Remove the reference dimension's current filter: Use REMOVEFILTERS(RefColumn). This step is crucial for controlling the context.

3. Apply the fixed reference: Set RefColumn = RefValue.

4. Calculate the difference: Finally, compute the difference as SelectedVal - RefValue.

 

This process allows for a clearer comparison against a specific reference value.

A subtle but important point about filter context

The provided DAX query evaluates a measure without a course filter context, which differs from how courses are represented in a visual report. The expression SelectedVal = BaseMeasure represents the current context, which may include all courses.

 

EVALUATE
    ROW (
        "Test Result",
        TestIncremental ( [Enrollments (Selected Course)], 'moodle3d mdl_course'[fullname], "Course 1" )
    )

 

The key point is that the reusable pattern isn't limited to the Course column and can involve other reference columns, opening up various scenarios.

Scenario 1 — Compare categories

Suppose the question changes from:

How is Course X doing compared with Course 1?

to:

How is Arts performing compared with Science?

We can reuse the same UDF.

 

EVALUATE
    CALCULATETABLE (
        ROW (
            "Category Incremental",
            IncrementalVsReference ( [Enrollments (Selected Course)], 'moodle3d mdl_course_categories'[name], "Science" )
        ),
        'moodle3d mdl_course_categories'[name] = "Arts"
    )

 

 

Scenario 2 — Compare periods instead of entities

 

How did August 2025 perform compared with April 2025?

We don't necessarily need a separate “reference month” measure.

We can point the same function at the date column.

 

EVALUATE
CALCULATETABLE(
    ROW(
        "Month Incremental (Aug25 vs Apr25)",
        IncrementalVsReference ( [Enrollments (Selected Course)], DimDate[Month Year], "Apr 2025" )
    ),
    DimDate[Month Year] = "Aug 2025"
)

 

 

 

A reference-comparison function doesn't have to compare entities. It can compare periods, categories, regions, products, or any other dimension represented by a column.

 

What about ALL() and VALUES()?

A reusable DAX pattern, ALL(VALUES(Column)), allows us to ignore local context when building candidate sets for ranking calculations. By combining the candidate set, label column, and ranking measure, we can efficiently identify top results without rewriting code for each scenario.

 

Generalize “Top Gainer” into a reusable ranking function.

Conceptually:

 

DEFINE
  FUNCTION MyTopByMeasure = ( Candidates: TABLE, LabelColumn: ColumnRef, RankMeasure: MeasureRef ) =>
    VAR RankedCandidates =
        ADDCOLUMNS ( Candidates, "@RankVal", CALCULATE ( RankMeasure ) )
    VAR TopRow =
        TOPN ( 1, RankedCandidates, [@RankVal], DESC )
    RETURN
        SELECTCOLUMNS ( TopRow, "Label", LabelColumn )

 

The TopByMeasure function adapts to various business questions including "Top Gainer Course" or "Highest Enrollment Course," without changing its core. Only the inputs vary.

 

Calling the ranking UDF

For example:

 

EVALUATE
  MyTopByMeasure (
    FILTER (
        ALL ( 'moodle3d mdl_course'[fullname] ),
        NOT ( 'moodle3d mdl_course'[fullname] IN { "testrun", "testA" } )
    ),
    'moodle3d mdl_course'[fullname],
    [Enrollments Incremental]
  )

 

 

The candidate set is provided separately from the ranking logic, allowing control over the candidate entities, label column, and ranking measure without rewriting the algorithm.

 

Why MeasureRef and ColumnRef matter

This concept is essential because while scalar parameters are fixed values, MeasureRef and ColumnRef are "lazy" (expr) parameters that allow evaluation within the function's context. This lets us pass measures and column references, enabling the function to adjust the filter context effectively. Consequently, the UDF signature offers greater flexibility than simply passing scalar values.

 

Where TABLEOF and NAMEOF fit in

DAX UDFs enhance capabilities for handling model metadata and references, with functions like TABLEOF identifying associated tables and NAMEOF returning names as text. They allow us to write more powerful DAX logic that treats model objects as parameters, beyond merely reducing lines.

UDFs also change how we think about semantic-model governance.

This promotes source control, standardizes business rules, and fosters a software engineering approach in DAX development.

Identify patterns to adapt DAX UDFs!