MAX DAX Function (Aggregation)
Returns the largest value in a column, or the larger value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| ColumnNameOrScalar1 |
The column in which you want to find the largest value, or the first scalar expression to compare. |
|
| Scalar2 | Optional |
The second value to compare. |
Return values
Largest value found in the column or in the two expressions.
Remarks
When used with a single column, the MAX function internally executes MAXX, without any performance difference.
The following MAX call:
MAX ( table[column] )
corresponds to the following MAXX call:
MAXX (
table,
table[column]
)
The result is blank in case there are no rows in the table with a non-blank value.
When used with two arguments, the syntax:
MAX ( exp1, exp2 )
corresponds to:
VAR v1 = exp1
VAR v2 = exp2
RETURN
SWITCH ( TRUE,
v1 = v2, IF ( ISBLANK ( v1 ), v2, v1 ),
v1 > v2, v1,
v2
)
Thus, when used with two arguments, MAX consider the BLANK value as significative, whereas MAXX ignores it.
-- MAX returns BLANK
MAX ( -2, BLANK() )
-- MAXX returns -2
MAXX ( { -2, BLANK() }, [Value] )
» 2 related functions
Examples
-- MAX is the short version of MAXX, when used with one column only
DEFINE
MEASURE Sales[MAX Net Price 1] = MAX ( Sales[Net Price] )
MEASURE Sales[MAX Net Price 2] = MAXX ( Sales, Sales[Net Price] )
MEASURE Sales[MAX Line Amount] =
MAXX ( Sales, Sales[Quantity] * Sales[Net Price] )
EVALUATE
SUMMARIZECOLUMNS (
'Product'[Color],
"MAX Net Price 1", [MAX Net Price 1],
"MAX Net Price 2", [MAX Net Price 2],
"MAX Line Amount", [MAX Line Amount]
)
| Color | MAX Net Price 1 | MAX Net Price 2 | MAX Line Amount |
|---|---|---|---|
| Silver | 2,879.99 | 2,879.99 | 9,996.00 |
| Blue | 3,199.99 | 3,199.99 | 12,799.96 |
| White | 3,199.99 | 3,199.99 | 12,799.96 |
| Red | 1,989.00 | 1,989.00 | 7,956.00 |
| Black | 2,499.00 | 2,499.00 | 9,996.00 |
| Green | 3,199.99 | 3,199.99 | 10,239.97 |
| Orange | 2,879.99 | 2,879.99 | 8,639.97 |
| Pink | 1,989.00 | 1,989.00 | 7,956.00 |
| Yellow | 789.75 | 789.75 | 2,369.25 |
| Purple | 104.89 | 104.89 | 419.56 |
| Brown | 3,199.99 | 3,199.99 | 12,799.96 |
| Grey | 3,199.99 | 3,199.99 | 12,799.96 |
| Gold | 605.70 | 605.70 | 2,356.00 |
| Azure | 290.00 | 290.00 | 1,160.00 |
| Silver Grey | 673.00 | 673.00 | 2,692.00 |
| Transparent | 2.94 | 2.94 | 11.76 |
-- MAX can be used with two scalars, in that case it
-- returns the maximum between its arguments
DEFINE
MEASURE Sales[Sales Amount] =
SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
MEASURE Sales[10K or Sales Amount] =
MAX ( 10000, [Sales Amount] )
EVALUATE
SUMMARIZECOLUMNS (
'Product'[Color],
"Sales Amount", [Sales Amount],
"10K or Sales Amount", [10K or Sales Amount]
)
ORDER BY [Sales Amount] ASC
| Color | Sales Amount | 10K or Sales Amount |
|---|---|---|
| Transparent | 3,295.89 | 10,000.00 |
| Purple | 5,973.84 | 10,000.00 |
| Yellow | 89,715.56 | 89,715.56 |
| Azure | 97,389.89 | 97,389.89 |
| Gold | 361,496.01 | 361,496.01 |
| Silver Grey | 371,908.92 | 371,908.92 |
| Pink | 828,638.54 | 828,638.54 |
| Orange | 857,320.28 | 857,320.28 |
| Brown | 1,029,508.95 | 1,029,508.95 |
| Red | 1,110,102.10 | 1,110,102.10 |
| Green | 1,403,184.38 | 1,403,184.38 |
| Blue | 2,435,444.62 | 2,435,444.62 |
| Grey | 3,509,138.09 | 3,509,138.09 |
| White | 5,829,599.91 | 5,829,599.91 |
| Black | 5,860,066.14 | 5,860,066.14 |
| Silver | 6,798,560.86 | 6,798,560.86 |
-- MAXX is needed to iterate the content of a variable,
-- indeed MAX works only with columns in the model
DEFINE
MEASURE Sales[Sales Amount] =
SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
MEASURE Sales[MAX Monthly Sales] =
VAR MonthlySales =
ADDCOLUMNS (
DISTINCT ( 'Date'[Calendar Year Month] ),
"@MonthlySales", [Sales Amount]
)
VAR FilteredSales =
FILTER ( MonthlySales, [@MonthlySales] > 10000 )
VAR Result =
-- Iterator required to aggregate the @MonthlySales column
MAXX ( FilteredSales, [@MonthlySales] )
RETURN
Result
EVALUATE
SUMMARIZECOLUMNS (
'Product'[Color],
"MAX Monthly Sales", [MAX Monthly Sales]
)
| Color | MAX Monthly Sales |
|---|---|
| Silver | 355,007.95 |
| Blue | 161,351.67 |
| White | 333,756.67 |
| Red | 77,069.70 |
| Black | 293,091.79 |
| Green | 93,839.92 |
| Orange | 69,139.50 |
| Pink | 86,940.06 |
| Grey | 169,268.89 |
| Silver Grey | 31,808.00 |
| Brown | 101,388.85 |
| Gold | 20,951.00 |
| Yellow | 13,791.93 |
Related articles
Learn more about MAX in the following articles:
-
Computing running totals in DAX
This article shows how to compute a running total over a dimension, like for example the date. » Read more
-
Understanding the difference between LASTDATE and MAX in DAX
This article explains why in many cases, MAX should be used instead of LASTDATE to search for the last date in a time period using DAX. » Read more
-
Computing MTD, QTD, YTD in Power BI for the current period
This article describes how to use the DAX time intelligence calculations applied to the latest period available in the data, also known as the “current” period. » Read more
Related functions
Other related functions are:
Last update: Nov 14, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber
Microsoft documentation: https://docs.microsoft.com/en-us/dax/max-function-dax