Dax Function: MAXX

Category: Aggregation functions

The MAXX function in Power BI is a DAX (Data Analysis Expressions) function used to return the maximum value of an expression evaluated over a table. Unlike the basic MAX or MAXA functions, which operate directly on a single column, MAXX allows for the evaluation of a calculated expression across a table or a subset of data.

Purpose:

  • Perform row-by-row evaluations on a table or subset, then find the maximum value of the results.
  • Useful for advanced calculations requiring custom expressions.

Type of Calculations:

  • Evaluates a dynamic expression row-by-row for a table.
  • Returns the maximum value among the results of the expression.

Practical Use Cases:

  1. Custom Calculations: Compare calculated fields across rows to find the highest value.
  2. Derived Metrics: Use conditional logic to derive metrics dynamically before finding the maximum.
  3. Cross-Column Evaluations: Perform comparisons or aggregations involving multiple columns.

MAXX(<table>, <expression>)</expression></table>
ParameterTypeDescription
tableTableA table containing the data to evaluate. It can be a full table or a filtered subset.
expressionScalarAn expression evaluated for each row of the table. Must return a numeric or date value.

How Does MAXX Dax Function Works?

  1. Row Context Evaluation:
    • The MAXX function iterates over each row of the table provided.
    • The expression is calculated for every row individually.
  2. Maximum Selection:
    • After evaluating the expression for all rows, the maximum value is selected and returned.

Example:
Given the table Sales with columns Quantity and Price, the function:

MAXX(Sales, Sales[Quantity] * Sales[Price])

Calculates the maximum value of Quantity * Price for all rows.

What Does It Return?

The MAXX function returns a scalar value that represents the maximum result of the expression evaluated for each row in the table. The result is a number or a date, depending on the expression.

When Should We Use It?

  • Custom Aggregations: When the maximum value depends on a calculation involving multiple columns.
  • Dynamic Filters: To find the maximum value in a subset of data filtered by other conditions.
  • Derived Metrics: When pre-calculated columns are unavailable, and on-the-fly calculations are required.

Examples

Basic Usage

Find the maximum revenue per row:


MaxRevenue = MAXX(Sales, Sales[Quantity] * Sales[Price])

Output:Returns the maximum revenue (Quantity * Price) from all rows.

Column Usage

Evaluate maximum growth percentage across regions:


MaxGrowth = MAXX(Regions, Regions[CurrentYearSales] / Regions[PreviousYearSales] - 1)

Result: Returns the highest growth percentage across all regions.

Advanced Usage

Combine with a filter to find the maximum sales in a specific category:


MaxCategorySales = MAXX(FILTER(Sales, Sales[Category] = "Electronics"), Sales[Quantity] * Sales[Price])

Result: Returns the highest revenue for the “Electronics” category.

Tips and Tricks

  • Use Filters Wisely: Combine MAXX with FILTER to focus on specific subsets of data.
  • Optimize Expressions: Simplify the expression for better performance on large datasets.
  • Avoid Non-Numeric Outputs: Ensure the expression returns numeric or date values for proper evaluation.

Performance Impact of MAXX DAX Function:

  • Row-by-Row Evaluation: MAXX performs row-by-row evaluations, which can be computationally expensive for large tables. Optimize by reducing the table size with FILTER or other techniques.
  • Avoid Overly Complex Expressions: Simplify expressions to enhance performance.

Related Functions You Might Need

  • MAX: Returns the maximum value in a single column.
  • MINX: Finds the minimum value of an expression over a table.
  • SUMX: Calculates the sum of an expression over a table.
  • AVERAGEX: Finds the average value of an expression over a table.
  • FILTER: Used to define subsets of data for evaluation.

Want to Learn More?
For more information, check out the official Microsoft documentation for MAXX. You can also experiment with this function in your Power BI reports to explore its capabilities.

If you’re looking to unlock the full potential of Power BI and take your data insights to the next level, our expert Power BI consulting services are here to help. Whether you need assistance with implementing advanced DAX functions like the ones discussed here, creating interactive dashboards, or optimizing your data models for better performance, our team of seasoned Power BI consultants is ready to provide tailored solutions for your business. Visit our Power BI Consulting page to learn more about how we can empower your organization with data-driven decisions.

1. What does the MAXX function do in Power BI?

The MAXX function calculates the maximum value of an expression evaluated for each row in a table.

2. How is MAXX different from MAX?

MAX operates on a single column, while MAXX evaluates a custom expression row-by-row over a table.

3. Can MAXX handle text or non-numeric values?

No, the expression in MAXX must return numeric or date values. Text values will cause errors.

4. When should I use MAXX?

Use MAXX when you need to calculate the maximum of a derived metric or expression involving multiple columns.

5. How can I improve MAXX performance on large datasets?

Apply filters to reduce the table size and optimize the expression to minimize computational overhead.

Previous article
Dax Function: MAXA
Next article
Dax Function: MIN