Understanding MAX, MAXA, and MAXX in DAX

Understanding `MAX`, `MAXA`, and `MAXX` in DAX in Power BI

1. `MAX` Function

Returns the largest numeric value from a column.
Works only with numeric data. Ignores non-numeric or blank values.

Example: Find the maximum sales amount from the Sales column.

Dataset:

Order ID	Sales
1	100
2	200
3	150
4	BLANK

DAX Formula:

Max Sales = MAX('Orders'[Sales])

Result:

200 (ignores the blank value).

2. `MAXA` Function

Returns the largest value from a column, but includes non-numeric values:
Blanks are treated as 0.
Text values are ignored unless explicitly convertible to numbers.

Example: Find the maximum value in the Sales column using MAXA.

Dataset:

Order ID	Sales
1	100
2	200
3	150
4	BLANK

DAX Formula:

MaxA Sales = MAXA('Orders'[Sales])

Result:

200 (treats the blank as 0, which doesn’t affect the result here).

Special Case for Text:
If the column has text that can be converted to numbers:

Order ID	Sales
1	100
2	“300”
3	150
4	“INVALID”

MAXA will consider "300" but ignore "INVALID".

Result: 300.

3. `MAXX` Function

Evaluates an expression for each row of a table and returns the maximum value of the results.

Example: Find the maximum profit per order, calculated as Sales - Discount.

Dataset:

Order ID	Sales	Discount
1	100	10
2	200	20
3	150	15

DAX Formula:

Max Profit = 
MAXX(
    'Orders',
    'Orders'[Sales] - 'Orders'[Discount]
)

Row-Wise Calculation:

Order ID	Profit (Sales – Discount)
1	90
2	180
3	135

Result:

180.

Comparison of Functions

Function	Purpose	Handles	Example Use Case
`MAX`	Finds the largest numeric value in a column.	Ignores blanks and non-numeric.	Find the maximum sales amount.
`MAXA`	Finds the largest value, treating blanks as `0`.	Includes convertible text.	Handle mixed-type data like “300” as text.
`MAXX`	Finds the largest value of an expression.	Evaluates a calculated result.	Calculate max profit per order dynamically.

Combined Example (Superstore Dataset)

Dataset:

Order ID	Sales	Discount	Category
1	100	10	Furniture
2	200	20	Office
3	BLANK	15	Technology
4	“300”	25	Office

DAX Measures:

Max Sales:

   Max Sales = MAX('Orders'[Sales])

Result: 200.

MaxA Sales:

   MaxA Sales = MAXA('Orders'[Sales])

Result: 300 (includes "300" as a valid value).

Max Profit (using MAXX):

   Max Profit = 
   MAXX(
       'Orders',
       'Orders'[Sales] - 'Orders'[Discount]
   )

Result: 180 (from Order ID 2).

By understanding these three functions, you can work with numeric, mixed-type, or calculated datasets effectively in Power BI.

Understanding MAX, MAXA, and MAXX in DAX