Easiest guide to differentiate MAX VS MAXX with examples in Power BI

When analyzing data in Power BI, understanding how to maximize your insights can make a world of difference. Power BI offers two pivotal functions for this task: MAX and MAXX. While they might seem interchangeable at first glance, each is designed for distinct scenarios. In this article, we will uncover the key differences between these two functions and provide practical examples to illustrate their unique applications.

MAX Function:

• Purpose: Returns the largest value in a column.
• Usage: Ideal when you need to find the highest value in a specific column.
• Syntax: MAX(<column>)
• Example: If you have a column “SalesAmount” in a sales table, MAX(Sales[SalesAmount]) will return the highest sales amount.

MAXX Function:

• Purpose: Returns the largest value that results from evaluating an expression for each row in a table.
• Usage: More versatile, used when you need to find the maximum value based on a condition or complex calculation.
• Syntax: MAXX(<table>, <expression>)
• Example: If you want to find the highest sales amount for products with sales above 100 units, you could use MAXX(Sales, IF(Sales[Units] > 100, Sales[SalesAmount], BLANK())).

Key Differences:

• Simplicity: MAX is straightforward and used for finding the highest value in a column.
• Flexibility: MAXX is more versatile, allowing for complex expressions and conditions.
• Application: Use MAX for simple maximum value calculations, and MAXX when you need to apply a condition or expression to each row in a table.

Building the KPI

We will go through these steps:

• Understanding the MAX Function
• Understanding the MAXX Function
• Comparing MAX and MAXX functions

Happy learning!

1. Understanding the MAX Function:

The table used:

We will be using the below table for understanding the use of MAX function:

The table is called MAX_function Table.

The table contains information about sales transactions. It has three columns: SalesAmount, Product, and Date.

Columns Explained

1. SalesAmount: This column shows the monetary value of each sales transaction.
2. Product: This column identifies the product associated with each sales transaction.
3. Date: This column records the date on which each sales transaction occurred.

Data Breakdown

• First Row:
• SalesAmount: 500
• Product: Product A
• Date: 2024-12-01
• Second Row:
• SalesAmount: 750
• Product: Product B
• Date: 2024-12-02
• Third Row:
• SalesAmount: 300
• Product: Product C
• Date: 2024-12-03

Interpretation

This table shows three sales transactions for different products on different dates, with the sales amounts recorded for each transaction. It can be used to analyze sales performance, track product popularity, or manage inventory based on sales data.

The DAX function used:

MAX func O/P = MAX(MAX_function[SalesAmount])

Explanation

MAX_function[SalesAmount]:

• This part of the formula is specifying the column SalesAmount from the table MAX_function.
• It tells the MAX function to look at the values in the SalesAmount column.

MAX(MAX_function[SalesAmount]):

• The MAX function is then used to find the maximum value in the SalesAmount column of the MAX_function table.
• This means it will return the highest value present in that column.

So, the above DAX will compute the maximum of SalesAmount column of the MAX_function table.

Example:

Result: The MAX func O/P measure calculates the maximum of the SalesAmount column, which would be:

MAX of (500, 750, 300) is 750

2. Understanding the MAXX Function:

The table used for MAXX function:

We will be using the below table for understanding the use of MAXX function:

The table is called MAXX_Function Table.

Columns Explained

1. Quantity: This column indicates the number of units of a product.
2. UnitPrice: This column shows the price per unit of the product.

Data Breakdown

• First Row:
• Quantity: 5
• UnitPrice: 100
• Second Row:
• Quantity: 10
• UnitPrice: 85
• Third Row:
• Quantity: 3
• UnitPrice: 100

Interpretation

This table can help analyze how pricing strategies change with different purchase quantities, and it can be used to understand and predict customer behavior based on pricing incentives.

The DAX function used:

MAXX func O/P = MAXX(MAXX_function, MAXX_function[Quantity]*MAXX_function[UnitPrice])

Explanation

MAXX(MAXX_function, MAXX_function[Quantity] * MAXX_function[UnitPrice]):

• The MAXX function evaluates an expression for each row in a table, and then returns the maximum value of these evaluations.
• Here, the expression is MAXX_function[Quantity] * MAXX_function[UnitPrice].
• This expression calculates the total sales value (Quantity multiplied by UnitPrice) for each row in the MAXX_function table.

Evaluation Process:

• The MAXX function goes through each row of the MAXX_function table.
• For each row, it computes Quantity * UnitPrice.
• It then finds the maximum value among these calculated sales values.

Example Data

Calculation

• For the first row: 5 * 100 = 500
• For the second row: 10 * 85 = 850
• For the third row: 3 * 100 = 300

Result

• The MAXX function will return the maximum value from these calculations, which is 850.

Summary

• Purpose: The DAX expression calculates the total sales value for each row and then returns the maximum of these values.
• Output: In this case, the output would be 850.

3. Comparing MAX and MAXX functions:

MAX Function

• Purpose: Returns the maximum value in a single column.
• Use Case: Ideal for straightforward maximum value retrieval tasks.
• Example: Finding the maximum sales amount from a SalesAmount column.
• DAX: MAX(Sales[SalesAmount])
• Calculation: Identifies the highest non-blank value in the SalesAmount column directly.
• Output: The maximum value of the sales amounts, e.g., 750.

MAXX Function

• Purpose: Evaluates an expression for each row in a table and then returns the maximum result.
• Use Case: Suitable for complex calculations requiring row-by-row evaluations before determining the maximum.
• Example: Calculating the maximum sales value by multiplying Quantity by UnitPrice for each row and then finding the highest result.
• DAX: MAXX(Sales, Sales[Quantity] * Sales[UnitPrice])
• Calculation: Multiplies Quantity by UnitPrice for each row and then identifies the highest calculated value.
• Output: The maximum of the calculated values, e.g., 750.

Key Differences:

Scope:

• MAX: Operates directly on a single column to find its maximum value.
• MAXX: Evaluates a custom expression for each row in the table, then finds the maximum of those evaluated values.

Flexibility:

• MAX: Limited to retrieving the maximum value from a single column of numeric data
• MAXX: Can perform more complex calculations involving multiple columns or expressions before determining the maximum value.

Use Cases:

• MAX: Simple and straightforward maximum value retrieval.
• MAXX: Required when you need to evaluate an expression for each row in a table before determining the maximum value.

Download the Data for MAX table from this link.

Download the Data for MAXX table from this link.

Thank you for your attention!

Follow me or subscribe to get all my Power BI articles!

Don’t forget to subscribe to

👉 Power BI Publication

👉 Power BI Newsletter

and join our Power BI community: