Daily DAX : Day 254 MINX

 The MINX function in Power BI DAX (Data Analysis Expressions) is used to evaluate an expression for each row of a table and return the smallest value from the results of that expression. It’s particularly useful when you need to find the minimum value of a calculated expression across a table or a filtered set of rows.

Syntax

dax

MINX(<table>, <expression>)

    <table>: The table over which the expression is evaluated. This can be a physical table, a table expression (like FILTER or ALL), or a table returned by another DAX function.

    <expression>: The expression to evaluate for each row of the table. This must return a scalar (single) value for each row.

Return Value

The smallest value resulting from evaluating the expression for each row in the specified table.

How It Works

    Iterates through the table: MINX evaluates the provided expression for each row in the specified table.

    Computes the expression: For each row, the expression is calculated, producing a scalar value.

    Returns the minimum: After evaluating the expression for all rows, MINX returns the smallest value among the results.

Use Cases

    Finding the minimum of a calculated measure:

        When you need to compute a value (e.g., a product of columns or a complex calculation) for each row and then find the smallest result.

        Example: Finding the minimum profit margin across products.

    Aggregating over related tables:

        MINX is useful when working with related tables (e.g., in a data model with relationships) to calculate a minimum value based on an expression involving multiple columns or tables.

    Conditional minimums:

        Combine MINX with FILTER to find the minimum value of an expression for a subset of rows based on specific conditions.

Example 1: Basic MINX Usage

Suppose you have a table called Sales with columns Product, Price, and Cost. You want to calculate the minimum profit (Price - Cost) across all products.

dax

MinProfit = MINX(Sales, Sales[Price] - Sales[Cost])

    Explanation: For each row in the Sales table, MINX calculates Price - Cost and then returns the smallest profit value.

Example 2: MINX with Filtering

You want to find the minimum sales amount for products sold in a specific region (e.g., "West") from a Sales table with columns Region, Product, and SalesAmount.

dax

MinSalesWest = MINX(

    FILTER(Sales, Sales[Region] = "West"),

    Sales[SalesAmount]

)

    Explanation: MINX evaluates the SalesAmount for each row where Region = "West" and returns the smallest SalesAmount in that filtered set.

Example 3: MINX with Related Tables

Suppose you have a Sales table and a related Products table with a Discount column. You want to find the minimum discounted price for each product.

dax

MinDiscountedPrice = MINX(

    Sales,

    Sales[Price] * (1 - RELATED(Products[Discount]))

)

    Explanation: For each row in the Sales table, MINX calculates the discounted price (Price * (1 - Discount)) using the related Discount column from the Products table and returns the smallest discounted price.

Key Points

    Performance: MINX iterates over each row in the table, so it can be resource-intensive for large datasets. Optimize by filtering the table (e.g., using FILTER) to reduce the number of rows processed.

    Comparison to MIN: Unlike the MIN function, which works directly on a single column, MINX allows you to evaluate an expression, making it more flexible for complex calculations.

    Context Awareness: MINX respects the filter context of the report or calculation, so the results may vary depending on slicers, filters, or other DAX measures.

Limitations

    The expression must return a scalar value.

    If the table is empty or the expression returns no valid values, MINX returns a blank result.

    MINX does not work with non-numeric expressions unless they can be coerced into numbers.

Practical Tips

    Use MINX when you need to compute a minimum based on a dynamic calculation rather than a static column.

    Combine with functions like FILTER, ALL, or RELATED to create powerful, context-aware calculations.

    For better readability, break complex expressions into calculated columns or measures before using MINX.