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:

1. Max Sales:

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

Result: 200.

2. MaxA Sales:

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

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

3. 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.