AXEL Filter & Context Functions

🔍 Filter & Context Manipulation

Control data scope and context with these 9 powerful functions.

FILTER

Filter table rows based on conditions.

COUNTX(FILTER([Monthly Report], [Year] = 2024), 1)

Power: Creates virtual filtered table
Use Cases: Conditional analysis, subset calculations
Returns: Filtered table

FILTER with COUNTROWS

Count rows matching specific criteria.

COUNTROWS(FILTER([Monthly Report], [Year] = 2024))

Clarity: More intuitive than COUNTX for counting
Use Cases: Conditional counts, criteria matching

ALL

Remove all filters from a table or column.

COUNTX(ALL([Monthly Report]), 1)

Power: Access unfiltered data
Use Cases: Grand totals, percentage of total calculations
Returns: Unfiltered table

ALLEXCEPT

Remove all filters except specified columns.

CALCULATE(
    SUM([Monthly Report].First_Class_Mail),
    ALLEXCEPT([Monthly Report], [Year])
)

Flexibility: Selective filter removal
Use Cases: Subtotals by dimension, partial context

VALUES

Get unique values from a column.

COUNTX(VALUES([Monthly Report].Month), 1)

Similar to: SQL DISTINCT
Use Cases: Dimension lists, unique value analysis
Returns: Single-column table

VALUE

Convert text to numeric value.

VALUE("123.45")

Purpose: Type conversion
Use Cases: Parsing strings, data cleanup
Returns: Numeric value

CALCULATE

Evaluate expression with modified filters.

CALCULATE(
    SUM([Monthly Report].First_Class_Mail),
    [Monthly Report].Year = 2024
)

Core Function: Heart of context manipulation
Use Cases: Conditional calculations, complex filtering
Returns: Scalar value

CALCULATETABLE

Return filtered table with modified context.

COUNTX(
    CALCULATETABLE(
        [Monthly Report],
        [Monthly Report].Year = 2024
    ),
    1
)

Table Version: Like CALCULATE for tables
Use Cases: Creating filtered datasets
Returns: Filtered table

COMPUTE

Alias for CALCULATE (alternative syntax).

COMPUTE(
    AVG([Monthly Report].Return_Mail),
    [Monthly Report].Month = "January"
)

Identical to: CALCULATE function
Use Cases: Same as CALCULATE, preference-based

💡 Advanced Patterns

Multi-Condition Filter

CALCULATE(
    SUM([Sales].Amount),
    [Sales].Year = 2024,
    [Sales].Status = "Completed"
)

Remove One Filter

CALCULATE(
    SUM([Sales].Amount),
    ALL([Products].Category),
    VALUES([Products].Brand)
)

Context Transition

SUMX(
    VALUES([Customer].ID),
    CALCULATE(SUM([Sales].Amount))
)

Percentage of Filtered Total

DIVIDE(
    SUM([Sales].Amount),
    CALCULATE(
        SUM([Sales].Amount),
        ALLSELECTED([Products])
    )
)

🎯 Common Scenarios

Scenario 1: Year-to-Date with Filters

CALCULATE(
    SUM([Sales].Amount),
    DATESYTD([Date].Date),
    [Product].Category = "Electronics"
)

Scenario 2: Ignore All Filters Except Date

CALCULATE(
    SUM([Sales].Amount),
    ALLEXCEPT([Sales], [Date])
)

Scenario 3: Dynamic Filtering

CALCULATE(
    AVG([Metrics].Value),
    FILTER(
        [Metrics],
        [Metrics].Value > AVERAGE([Metrics].Value)
    )
)

📊 Performance Tips

FILTER creates virtual tables - use sparingly in large datasets
ALL can be memory intensive - consider ALLSELECTED
CALCULATE changes context - understand row vs filter context
Combine filters in single CALCULATE when possible

AXEL Filter & Context Functions - CALCULATE, FILTER & More