Power BI MAX and MIN function with Examples

In this Power bi tutorial, we will learn the Power bi Max and Min aggregation functions. And also we will discuss the below points:

• Power bi Min function
• Power bi MINA function
• Power bi MINX function
• When to use Power bi MIN, MINA, and MINX function
• Power bi MAX function
• Power bi MAXA function
• Power bi MAXX function
• When to use Power bi MAX,MAXA, and MAXX function
• Power bi MIN function example
• Power bi Max function example

Power bi Min function

Power bi Min function returns the smallest value in a column, or between two scalar expressions.

The syntax of Power bi MIN()

MIN = MIN(<column>)

MIN = MIN(<expression>, <expression>)

Read Power BI DAX Logical functions [7 Examples]

Power bi MINA function

Power bi MINA function returns the smallest value in a column, the column for which you want to find the minimum value.

The Power bi MINA function takes as an argument a column that contains the number and determines the smallest value as follows:

• If the columns contains no value, then the function returns 0.
• Rows in the column that evaluates to logical values, such as true and false, If true treated as 1 and if false treated as 0.
• Empty/ blank cells are ignored by function.

The syntax of the Power BI MINA function:

MINA = MINA(< column>)

Power bi MINX function

The power bi MINX function returns the smallest value that results from evaluating an expression for each row of a table.

Whereas the table containing the rows for which expression will be evaluated. And the expression to be evaluated for each row of the table.

The Power bi MINX(), takes as its first argument as a table, or an expression returns a table, and the second argument contains the expression that is evaluated for each row of the table.

The Syntax of the MINX function in Power BI:

MINX= MINX(<table>, <expression>)

Read Power BI DAX SUM and SUMX function – How to use

When to use Power bi MIN, MINA, and MINX function

Here we will see when to use Power bi MIN, MINA, and MINX functions with examples in power bi desktop.

We will use the below sample table to see when to use Power bi MIN, MINA, MINX functions.

MIN function

The Power bi MIN function is used when:

• You want to calculate the MIN of Date, Number, and text data type.
• It does not support logical value.
• If you want to compare the two measure you can use min function.

Let’s see how to use Power BI MIN() function with an example.

MIN() of Date type:

We will calculate the MIN of the order date by using the measure in power bi desktop.

To calculate the MIN of Date u can use the below date

Min date = MIN(Sheet[Order date])

We will calculate the MIN of profit by using measures, which will show the Minimum profit.

To calculate the Min of profit writes the below measure:

Min profit = MIN(Sheet[Profit])

Min Of text type:

We will calculate the min of the product by using a measure in power bi.

To calculate the min of product, write the below measure:

Min Text = MIN(Sheet[Product ])

Compare two measures using MIN()

Here we have two measures i.e total sales and total profit which we will compare using Min() using Measure.

Total profit = SUM(Sheet[Profit])

Total sales = SUM(Sheet[Sales])

To compare the two measures using Min() function, write the below measure

Compare2 measure = MIN([Total profit],[Total sales])

MINA function

We will use the Power BI MINA() function when we want

• To calculate the MIN of Date, Number, and boolean type data type
• And it does not support the text type, it return 0. Use MIN() to calculate the text type data type.

Let’s see how to use MINA() function in Power BI with an example

MINA of Date type

We will calculate the MINA of order date using the measure in Power bi desktop.

To get the MINA of the order date, write the below measure

MinA of date = MINA(Sheet[Order date])

MINA of number type

We will calculate the MINA of sales using the measure in Power bi desktop.

To get the MINA of sales, write the below measure

MinA of sales = MINA(Sheet[Sales])

MINA of Boolean type

We will calculate the MINA of boolean type using a Power BI measure, if it is true it will return 1 and if it is false it will return 0.

To calculate the boolean type data type, write the below measure

MINA BOOLean = MINA(logical[Boolean])

MINX function

We will use the Power BI MINX function when we want :

• To calculate the MINX of Date, Number, and text type data type.
• And it does not support boolean type data type
• We can use the filter condition in MINX function.

Let’s see how to use the Power BI MINX function with examples.

MINX of Date type

We will calculate the MINX of order date using the measure in Power bi desktop.

To calculate the MINX of date, then write the below measure:

MINX of date = MINX(Sheet, Sheet[Order date])

MINX of number type

We will calculate the MINX of sales using a measure in power bi desktop.

To calculate the MINX of sales, write the below measure:

MINX of Sales= MINX(Sheet, Sheet[Sales])

MINX of text type

We will calculate the MINX of the product using a measure in Power bi desktop.

To calculate the MINX of text type, write the below Power BI measure.

MINX of text = MINX(Sheet, Sheet[Product ])

Read Power bi measure examples (20 useful examples)

Power bi MAX function

The Power BI MAX function returns the largest value in a column or between two scalar expressions. When comparing the two expressions blank is treated as 0.

If both the argument is blank in expression then max function return blank.

The Syntax of Power bi Max function

Max= MAX(<column>)

Max = MAX(<expression 1>,<expression 2>)

Power Bi MAXA function

The Power Bi MAXA function returns the largest value in a column.

The Power bi MAXA function takes as argument a column and looks for the largest value among the following types of values

• Numbers
• Dates

The power bi MAXA function supports logical values such as true and false, if the rows are true count as 1, and if the rows are false count as 0.

The power bi MAXA functions ignore the empty cells and if the column contains no values then it returns 0.

The syntax of the Power BI MAXA function:

MAXA(<column>)

Read Power bi measure count with filter

Power bi MAXX function

The Power bi MAXX function evaluates an expression for each row of a table and returns the largest value,

In the Power bi MAXX function, the table argument can be a table name or an expression that evaluates a table name. The second argument in the MAXX function indicates the expression to be evaluated for each row of the table.

The Syntax of power bi MAXX function:

MAXX = MAXX(<table>,<expression>)

When to use Power bi MAX, MAXA, and MAXX function

Here we will see when to use Power bi MAX, MAXA, and MAXX functions.

We will use the below sample table to learn when we will use the Power bi MAX, MAXA, and MAXX functions.

MAX function

We will use the MAX function when we want to calculate the

• Date, Number, and Text data type
• Does not calculate the logical value
• We can compare the two measure value

Let’s see how to use MAX() function in Power BI with an example

Max function of Date data type

Here we will calculate the Max of order date using a measure in Power bi desktop.

To calculate the max of the order date, then write the below measure.

Max = Max(Sheet[Order date])

Max function of Number data type

Here we will calculate the Max of sales using the measure in power bi desktop.

To calculate the MAX of sales, write the below measure:

Max = Max(Sheet[Sales])

Max function of Text data type

Here we will find the max of product name by using the measure in power bi desktop.

To calculate the MAX of product, then write the below measure:

Max = Max(Sheet[Product ])

Compare two measure values using Max function

Here we will compare the two measures using the MAX function in power bi desktop.

We will use the below two measures to compare two measure values using the MAX function and return the highest value.

Total profit = SUM(Sheet[Profit])

Total sales = SUM(Sheet[Sales])

To compare the above two measures, write the below measure:

Max = Max(Sheet[Total profit], Sheet[Total sales])

MAXA function

We will use the Power BI aMAXA function to calculate the

• Date data type, Number data type and boolean data type.
• It does not not calculate text data type, it will return 0.

Let’s see how to use the MAXA function with an example

MAXA function of date data type

Here we will calculate the MAXA of order date using a measure in power bi desktop.

To find the MAXA of the order date, write the below measure:

MaxA = MAXA(Sheet[Order date])

MAXA function of number data type

Here we will calculate the Power BI MAXA of sales using the measure in power bi desktop.

To calculate the MAXA of sales, write the below measure:

MaxA = MAXA(Sheet[Sales])

MAXA of Boolean data type

Here we will find the MAXA of boolean data type using measure in power bi desktop.

To calculate the MAXA of boolean data type, then write the below measure.

MAXA BOOLean = MAXA(logical[Boolean])

MAXX function

We will use the Power BI MAXX function to calculate the

• Date, Number and text data type
• It cannot calculate the logical values
• With MAXX function we can use the filter condition.

Let’s see how to use the MAXX function with examples

MAXX of the date function

We will calculate the MAXX of order date using the measure in Power bi desktop.

To calculate the Maxx of the order date, write the below measure:

Maxx = MAXx(Sheet, Sheet[Order date])

Maxx of number data type

We will see how to find the Max of sales using the measure in power bi desktop.

To calculate the MAXX of sales, write the below measure:

Maxx = MAXx(Sheet, Sheet[Sales])

MAXX of text data type

We will see how to find Maxx of product name using a measure in power bi desktop.

To calculate the Maxx of product, write the below measure:

Maxx = MAXX(Sheet, Sheet[Product ])

Use the filter condition with the MAXX function

Here we will see how to use filter condition with the MAXX function using measure in Power bi desktop.

To calculate the MAX sales of product A, for this write the below measure:

Maxx = MAXX(FILTER(Sheet,Sheet[Product ]="AAA"),Sheet[Sales])

Read How to get selected value from Slicer in Power BI

Power bi MIN function example

Here we will see the different types of examples of MIN, MINA, and MINX function in power bi.

Example 1: Calculate the smallest value in the range

Here we will calculate the smallest value using the MIN function in the Power bi desktop.

We will use the below sample data to calculate the minimum value.

• Load the data using get data
• Then write the below measure to find the minimum value:

Lowest value = MIN('Sheet 1'[Test score])

Read Power BI Measure multiply with examples

Example 2: How to find columns minimum value based on the other columns group

Here we will calculate the minimum transit days for Plant A using the MINX function in Power BI.

We will use the below sample data, having three column plant names, regions, and transit days.

Now to calculate the minimum transit days for plant A in power bi desktop.

• Load the data using get data
• Then write the below measure

MIn transit days for A = MINX(FILTER('Table', 'Table'[Plant] ="A"), 'Table'[Transit days])

Read Power bi sort by measure

Example 3: How to get sum by filtering the minimum date

Here we will calculate the sum of average inventory by filtering the minimum date using measure in Power bi desktop

We have two tables, that is inventory table and the date table.

Inventory table

Date table: Created by using calculate d table, you can write the below formula

Date = CALENDAR(DATE(2019,10,20), Date(2019,11,8))

Now we will find the sum of the average inventory of min date.

• Load the data using get data
• Click on the new measure from the ribbon
• Then write the below measure

Measure_ = 
VAR MinDateFromSelectedInterval = MIN('Date'[Date])
VAR MinAvailableDateWithINventory = CALCULATE(MIN('Data'[Date]), 'Data'[Date] >= MinDateFromSelectedInterval)
RETURN
CALCULATE(SUM('Data'[Average inventory]), FILTER('Data', 'Data'[Date] = MinAvailableDateWithINventory))

Read Power bi measure divide + 8 examples

Power bi Max function example

Here we will see different examples of power bi MAX, MAXA, MAXX function.

Example 1: Calculate the highest value from the range

Here we will calculate the highest value from the range using max function in power bi desktop.

We will use the below sample table, having two columns student and test score.

• Now load the data using get data
• Then click on measure from the ribbon
• Then write the below measure

Highest value = MAX('Sheet 1'[Test score])

Read Power BI Measure Date + Examples

Example 2: Calculate the MAX for multiple column

Here we will calculate the MAX value for more than one column using the MAXX() in measure in power bi desktop.

We will use the below sample data having four columns i.e item, col1, col2, and col3.

Now we will calculate the MAX from multiple columns using the Power VI MAXX function.

• Load the data using get data
• Then click on the new measure from the ribbon
• Then write the below measure:

Max of more than 2 columns Measure = 
MAXX(
    {
        MAX('Table (2)'[Col1]),
        MAX('Table (2)'[Col2]),
        MAX('Table (2)'[Col3])
    },
    [Value]
)

Read Power BI Measure Filter

Example 3: Calculate the MAX value per category

Here we will calculate the MAX value per category using MAX() in Power bi desktop

We will use the below sample data having two columns item or column and value.

To calculate the MAX value from the value column based on the category.

• Load the data using get data
• Then click on the new measure from the ribbon
• Then write the below measure

Measure = CALCULATE( MAX(Table3[Value]),ALLEXCEPT(Table3, Table3[Column ]))

In this power bi tutorial, we learned 6 types of aggregation functions i.e. MIN, MINA, MINX, MAX, MAXA, and MAXX with examples.

You may also like the following Power BI tutorials:

• Power bi measure switch statement with examples
• Countif function in Power BI Measure + 10 Useful Examples
• Power BI Measure SUM
• Power bi measure subtract + 7 useful examples
• Power bi measure by category + Examples
• How To Use Azure Text Analytics In Power BI

And also we discuss the below points:

• Power bi MIN function
• Power bi MINA function
• Power bi MINX function
• When to use Power bi MIN, MINA, and MINX function
• Power bi MAX function
• Power bi MAXA function
• Power bi MAXX function
• When to use Power bi MAX,MAXA, and MAXX function
• Power bi MIN function example
• Power bi Max function example