Power BI Dax Max Function [With Examples]

In the Power Bi tutorial, we will learn how to use Power BI Dax Max Aggregation Function in Power BI.

In a recent Power Bi project, I had to calculate the values using Power BI Max aggregation function. So in this Power BI tutorial, I have explained to you how to calculate the maximum value for the Power Bi report. Also covered below topics:

1. What is the Power BI DAX function max?
2. Difference between Power bi Dax max vs Maxx
3. How to filter max value in Power bi Dax
4. Power bi Dax max if
5. How to find the second highest value Power bi, Dax.
6. How to calculate Power bi Dax max value per category
7. Power bi Dax max value group by
8. Power BI DAX max selected value
9. How to calculate Power BI DAX max month
10. How to find Power BI DAX max of two columns
11. How to use the max all power BI DAX
12. How to get the max value from another table in Power BI DAX
13. Power BI DAX gets max value per group
14. Find max year in Power BI DAX
15. How to calculate the sum of max in Power bi Dax
16. Power BI DAX max previous month
17. Calculate max Qatar month in Power BI DAX

If you are new to Power BI DAX, check out an article on Power BI DAX (Data Analysis Expressions) tutorial

Power BI DAX function max

Let us see how we can find the maximum value using the Power bi DAX Max function in Power Bi.

In this example, we will use vehicle data shown below, which contains four columns called Car Names, Car Model, Price, and Color. To find the maximum price value of the car presented in the car’s table data.

The Power BI MAX function takes a column or two expressions as an argument and returns the largest value.

Power Bi Syntax for Maximum function:

MAX(<column>)

• Open the Power Bi desktop, and load the data into the desktop. Once the data has been loaded, select the new measure option from the ribbon under the Home tab and apply the below mentioned formula.

Maximum Value = MAX(Cars[Price])

Where,

1. Maximum Value = New Measure name
2. Cars = Table Name
3. Price = Existing column name

• Now from the visualization, select the Table visual and card visual in the report section.
• In the table visual and then drag and drop car names, car models, and price field values.
• In the card visual drag and drop the created measure value to display the maximum price value in the Power BI report.
• The screenshot below displays the maximum price value in the card visual in the Power Bi report.

This is how to find the maximum value using the Power bi DAX Max function in Power Bi.

Power bi Dax max vs Maxx

Power Bi Dax Max	Power Bi Dax Maxx
Power Bi Dax Max function returns the largest value in a column or the larger value between two scalar expressions.	Power Bi Dax Maxx function returns the largest value for an expression evaluated for each row in a table
Syntax: MAX(<column>) , MAX(<expression1>, <expression2>)	Syntax : MAXX(<table>,<expression>)

Read Power bi Dax Today() Function

Power bi Dax filter max value

Let us see how we can filter the maximum value using the Power bi Dax Max and a Filter function in Power BI

In this example, we will filter the maximum car price value for the mentioned car model. If the car model didn’t match then the card visually displays the blank value.

• Open the Power Bi desktop, and load the data into the desktop. Once the data has been loaded, select the new measure option from the ribbon under the Home tab and apply the below mentioned formula.

Filter Max = MAXX(FILTER(Cars,Cars[Car Model]="Q7"),Cars[Price])

Where,

1. Filter Max = New Measure name
2. Cars = Table Name
3. Price, Car Model = Existing column name

• Now from the visualization, select the Table visual and card visual in the report section.
• In the table visual and then drag and drop car names, car models, and price field values.
• In the card visual drag and drop the created measure value to display the maximum price value based on the selected car model in the Power BI report.
• The screenshot below displays the maximum price value of the mentioned car model by default in the card visual in the Power Bi report.

• In the same way, if we select any other car model from the table visual it displays the blank value because the selected car model will not match the mentioned car model.

This is how to filter the maximum value using the Power bi Dax Max and a filter function in Power BI.

Read Power BI DAX Calendar Function

Power bi Dax max if

Let us see how to find the maximum value using the max if function in Power BI

In this example, we will display the car name in the card visual, if the car price value is greater than or equal to 1000000 else it will display the blank value.

• Open the Power Bi desktop, and load the data into the desktop. Select the new measure option from the ribbon under the Home tab and apply the below-mentioned formula.

Max If = 
      VAR maximumvalue = MAX(Cars[Car Names])
      RETURN
      CALCULATE(MAX(Cars[Price]),
                FILTER(ALL(Cars),
                Cars[Car Names] = maximumvalue && Cars[Price] >= 1000000 ))

Where,

1. Max If = New Measure name
2. Cars = Table Name
3. Price, Car Names = Existing column name

• Now from the visualization, select the Table visual and card visual in the report section.
• In the table visual and then drag and drop car names, car models, and price field values.
• In the card visual drag and drop the created measure value to display the maximum price value based on the condition applied.
• The screenshot below displays the car name in the card visual for the applied condition.

• If we select the lesser car price value then it displays the blank value in the card visual based on the condition applied below:

This is how to find the maximum value using the max if function in Power BI.

Power bi Dax’s second highest value

Let us see how we can find the second highest value in the table using the Power BI Dax function in Power BI

In this example, we will find the second highest value of the car price using the Power Bi Dax function.

• Open the Power Bi desktop, and load the data into the desktop. Select the new table option from the ribbon under the Home tab and apply the below-mentioned formula.

Second Max Value = TOPN(2, Cars,Cars[Price])

Where,

1. Second Max Value = New Table name
2. Cars = Table Name
3. Price = Existing column name

• In the new table, we can see the second maximum value ( highlighted in red) from the cars table it filters the two maximum cars’ values based on the car price

This is how to find the second-highest value in the table using the Power BI Dax function in Power BI.

Read Power BI DAX Filter Table

Power bi Dax max value per category

Let us see how we can display the maximum value based on the category in Power Bi,

Here we will find the maximum car price value based on the selected value from the table visual.

• Open the Power Bi desktop, and load the data into the desktop. Select the new measure option from the ribbon under the Home tab and apply the below-mentioned formula.

Max Value Per Category  = 
VAR MaxPrice =
    CALCULATE (
        MAX ( Cars[Price])
    )
RETURN
    CALCULATE (
        MAX ( Cars[Car Model] ),
        FILTER (
            ALLEXCEPT ( Cars, Cars[Car Model] ),
            Cars[Price] = MaxPrice
        )
    )

Where,

1. Max Value Per Category = New Measure name
2. Cars = Table Name
3. Price, Car Model = Existing column name

• Now from the visualization, select the Table visual and card visual in the report section.
• In the table visual and then drag and drop car names, car models, and price field values.
• In the card visual drag and drop the created measure value to display the maximum price value based on the condition applied.
• The screenshot below displays the car model in the card visual for the maximum car price value.

This is how to display the maximum value car model based on the category selected in the table visual in Power Bi.

Read How To Remove Filter From Power BI DAX

Power bi Dax max value group by

Let us see how we can find the maximum value based on the group in Power BI,

In this example, we will calculate the maximum value for the group and display the maximum car price value as 1 and the remaining value as 0.

• Open the Power Bi desktop, and load the data into the desktop. Select the new Column option from the ribbon under the Home tab and apply the below-mentioned formula.

Max in group = IF(CALCULATE(MAX(Cars[Price]),ALLEXCEPT(Cars,Cars[Car Model],Cars[Car Names]))=MAX(Cars[Price]),1,0)

Where,

1. Max in group = New Column name
2. Cars = Table Name
3. Price, Car Model, Car Names = Existing column name

• In the new column, you can see the maximum value is represented as 1 and the remaining values as 0 as shown below:

This is how to find the maximum value based on the group in Power BI.

Power BI DAX max of two columns

Let us see how we can display the maximum value of two columns using the Power Bi max function in Power Bi,

In the example, we will find the maximum price value of two or more columns called cost price and selling price and display the maximum value in the new column,

• Open the Power Bi desktop, and load the data into the desktop. Select the new Column option from the ribbon under the Home tab and apply the below-mentioned formula.

Maximum Value  = MAXX(Cars,MAX(Cars[Cost Price],MAX(Cars[Selling Price])))

Where,

1. Maximum Value = New Column name
2. Cars = Table Name
3. Cost Price, Selling Price = Existing column names

• In the below screenshot, you can see that the new column displays the maximum value of two different columns in Power Bi,

This is how to display the maximum value of two columns using the Power Bi max function in Power Bi.

Read Power BI DAX Filter If

Power BI DAX max month

Let us see how we can find the Max Month value using the Power Bi Dax max function in Power BI,

Here in this example, if the Max month presented in the table matches with the current month then it will display true in the card visual else false.

• Open the Power Bi desktop, and load the data into the desktop. Select the new measure option from the ribbon under the Home tab and apply the below-mentioned formula.

MaxMonth = 
Var MaximumMonth = MONTH((TODAY()))
RETURN
if(MONTH(MAX(Cars[Released Date].[MonthNo]))=MaxMonth,TRUE(),FALSE())

Where,

1. MaxMonth = New Measure name
2. Cars = Table Name
3. Released Date = Existing column name
4. MaximumMonth= Variable name

• Now in the report section, select the table visual and card visual. In the table visually drag and drop the car name, car price, and released date value from the field pane.
• And in the card visual drag and drop the created measure value to display the maximum value based on the condition applied.
• The screenshot below displays the value as false in the card visual, because the Car table Released date maximum month is not matched with the current month value (ie, the Current Month is November).

This is how to find the Max Month value using the Power Bi Dax max function in Power BI.

Read Power BI Dax Filter

Power BI DAX max selected value

Here we will see how we can display the maximum value based on the selected value in Power BI,

In this example, we will use the Power Bi Max function to calculate the maximum value and we use the slicer visual for the selection process.

• Open the Power Bi desktop, and load the data into the desktop. Select the new measure option from the ribbon and apply the below formula to calculate the maximum value.

Maxvalue = MAX(Cars[Cost Price]) 

Where,

1. Max value = New Measure name
2. Cars = Table Name
3. Cost Price = Column name

• Now in the report section, select the slicer visual and card visual. In the slicer visual drag and drop the car name field from the field pane.
• And in the card visual drag and drop the created measure value to display the maximum value based on the condition applied.
• In the below screenshot you can see that the card visually displays the maximum value based on the slicer selected value:

• In the same way, if we choose a different car name from the car visual it displays the maximum value accordingly.

This is how to display the maximum value based on the selected value in Power BI.

Read How to Filter Power BI Dax Based On Condition

Power BI DAX max all

Here we will see how to find the maximum value using the Power BI DAX max-all function in Power BI,

We will find the maximum price value of the car presented in the car’s table data.

• Open the Power Bi desktop, and load the data into the desktop. Select the new measure option from the ribbon and apply the below formula to calculate the maximum value.

Max All = CALCULATE (MAX(Cars[Cost Price]),ALL(Cars))

Where,

1. Max value = New Measure name
2. Cars = Table Name
3. Cost Price = Column name

• Now in the report section, select the table visual and card visual. In the table visually drag and drop the car name, car model, and price fields from the field pane.
• And in the card visual drag and drop the created measure value to display the maximum value based on the condition applied.
• In the below screenshot you can see that the card visually displays the maximum value based on the condition applied:

This is how to find the maximum value using the Power BI DAX max-all function in Power BI.

Power BI DAX gets the max value from another table

Let us see how we can find the maximum value from another table using power Bi DAX in Power BI.

In this example, we are going to use two tables called (Bikes and Cars) to calculate the maximum value and display the result in the card visual.

• Open the Power Bi desktop, and load the data into the desktop. Select the new measure option from the ribbon and apply the below formula to calculate the maximum value.

MaxValueFromTable =
 VAR Carid = Cars[Car Colors] return MAXX(
  FILTER(
    Bikes,
   Bikes[Bike Colors] = Carid
  ),
   Bikes[Price]
)

Where,

1. MaxValueFromTable = New Measure name
2. Cars, Bikes = Table Name
3. Bike Colors, Car Colors, Price = Column name
4. Carid = Variable id

• Now in the report section, select two table visuals and one card visual. In the table visually drag and drop the car name and car color field from the field pane, and in another table visually drag and drop the bike name, bike price, and bike color fields from the field pane.
• And in the card visual drag and drop the created measure value to display the maximum value based on the condition applied.
• In the below screenshot, you can see that the card visually displays the maximum value based on the color value selected from the car table visual to display the bike price value.

This is how to find the maximum value from another table using power Bi DAX in Power BI.

Read How to Filter Date using Power BI DAX

Power BI DAX gets max value per group

Let us see how to get the max value based on the group using Power Bi Dax in Power BI

In this example, we are going to filter the maximum value and display the result in the new column for the existing table.

• Open the Power Bi desktop, and load the data into the desktop. Select the new column option from the ribbon and apply the below formula to calculate the maximum value.

Max in group = IF(CALCULATE(MAX(Bikes[Price]),ALLEXCEPT(Bikes,Bikes[Bike Names],Bikes[Color]))=MAX(Bikes[Price])
          ,1,0)

Where,

1. Max in group = New column name,
2. Bikes = Table Name
3. Bike Colors, Price = existing Column names

• It groups the value presented in the table, and displays the maximum price value as 1 in the new column else it displays the value as 0.
• In the below screenshot, you can see that the new column displays the maximum group value as 1 else 0.

This is how to get the max value based on the group using Power Bi Dax in Power BI.

Power BI DAX max year

Let us see how we can calculate the max year value using the Max function in Power BI

• Initially, open the Power Bi desktop, and load the data into the desktop. Select the new measure option from the ribbon and apply the below formula to calculate the maximum year value in Power Bi.

Max Year = MAX(Cars[Released Date].[Year])

Where,

1. Max Year= New measure name,
2. Cars = Table Name
3. Released Date = existing Column name

• Now in the report section, select table visuals and card visuals. In the table visually drag and drop the car name and Released date field from the field pane.
• And in the card visual drag and drop the created measure value to display the maximum value based on the condition applied.
• In the below screenshot, you can see that the card visually displays the maximum year value presented in the table.

This is how to calculate the max year value using the Max function in Power BI.

Read Difference between USERNAME() and USERPRINCIPALNAME() in Power BI Dax

Power bi Dax sum of max

Here we will see how to find the Power Bi sum of the max value using the Power Bi Dax function in Power Bi

In this example, we will sum the maximum value presented in the cars table data and display the result value in the card visual.

• Open the Power Bi desktop, and load the data into the desktop. Select the new measure option from the ribbon and apply the below formula.

Sum Max = SUMX(
SUMMARIZE(Cars, Cars[Car Names], "Max Sales", MAX(Cars[Cost Price])),Cars[Max All])

Where,

1. Sum Max = New measure name,
2. Cars = Table Name
3. Car Names, Cost Price = existing Column name

• Now in the report section, select table visuals and card visuals. In the table visually drag and drop the car name, car model, and Cost price fields from the field pane.
• And in the card visual drag and drop the created measure value to display the maximum value based on the condition applied.
• In the below screenshot, you can see that the card visually displays the sum maximum value presented in the table.

This is how to find the sum of the max value using the Power Bi Dax function in Power Bi.

Power BI DAX max previous month

Let us see how we can display the maximum previous month’s value using a max function in Power Bi.

• Open the Power Bi desktop, and load the data into the desktop. Select the new column option from the ribbon and apply the below formula.

Previous Max Month = 
VAR MaxVal =
    FILTER (
        ALL ( 'Cars' ),
        Cars[Released Date] = EARLIER ( Cars[Released Date] )
            && Cars[Car Names] = "Honda"
    )
RETURN
    MAXX ( MaxVal, Cars[Released Date] )

Where,

1. Previous Max Month = New column name,
2. Cars = Table Name
3. Car Names, Released Date = existing Column name

• In the below screenshot, you can see that the new column displays the max value of the previous month value. (ie where the current month value is 12).
• You can see that Month value 11 has two dates in the table, but in those two values, it displays the maximum month value.

This is how to display the maximum previous month’s value using a max function in Power Bi.

Read Power bi DAX functions with examples

Power BI DAX max Qatar month

Let us see how we can display the maximum Qatar month value using a max function in Power Bi.

• Open the Power Bi desktop, and load the data into the desktop. Select the new column option from the ribbon and apply the below formula.

Max Date Value = CALCULATE (
    SUM ( Cars[Price] ),
    FILTER ( 'Cars', Cars[Released Date] = MAX ( Cars[Released Date] ) )
)

Where

1. Max Date Value= New column name,
2. Cars = Table Name
3. Car Names, Released Date = existing Column name

• In the below screenshot, you can see that the new column displays the max price value of the car for the Qatar month value. (ie where the current month value is 12).

This is how to display the maximum Qatar month value using a max function in Power Bi.

This Power BI tutorial explained how to easily calculate the Maximum values from the table data. Also, we covered these topics below:

• Power bi Dax max vs Maxx
• Power BI DAX function max
• Power bi Dax filter max value
• Power bi Dax max if
• Power bi Dax’s second highest value
• Power bi Dax max value per category
• Power bi Dax max value group by
• Power BI DAX max selected value
• Power BI DAX max month
• Power BI DAX max of two columns
• Power BI DAX max all
• Power BI DAX gets the max value from another table
• Power BI DAX gets max value per group
• Power BI DAX max year
• Power bi Dax sum of max
• Power BI DAX max previous month
• Power BI DAX max Qatar month

You may like the following Power BI tutorials:

• Power BI Switch – DAX function
• Subtraction in Power bi using DAX
• Power BI DAX Max Date [With 15+ Real Examples]