In the DAX (Data Analysis Expressions) language, used mainly in Power BI, SQL Server Analysis Services (SSAS), and Power Pivot for Excel, the MINX and MAXX functions are aggregation functions that respectively calculate the minimum and maximum value of an expression evaluated on a specified data set.
Let’s take a look at how these two functions are used.
MINX function
The MINX function returns the smallest resulting value of an expression evaluated on a specified table.
Syntax :
Parameters:
- <table>: A table or expression that returns a table on which the expression will be evaluated.
- <expression>: A DAX expression whose values will be evaluated for each row of the specified table.
Example:
Let’s imagine a table named Sales with columns ProductID, Quantity, and Price. If we want to find the minimum price among the products, we can use :
MAXX function
The MAXX function returns the largest resulting value of an expression evaluated on a specified table.
Syntax :
Parameters:
- <table>: A table or expression that returns a table on which the expression will be evaluated.
- <expression>: A DAX expression whose values will be evaluated for each row of the specified table.
Example:
Using the same Sales table, if we want to find the maximum price among the products, we can use :
Use in complex scenarios
These functions can also be used in more complex scenarios where the expression is a combination of columns or a calculated formula. For example, to find the minimum and maximum date after using a periodic filter, we can write :
Example
Conclusion
The MINX and MAXX functions are very powerful, enabling in-depth analysis by finding minimum and maximum values on expressions calculated for each row of a given table. They are commonly used in reports and data models to provide insights and detailed analysis.