Power BI Max

What is the Power BI MAX function?

The Power BI MAX is an aggregation function that returns a single most significant value in a column or between two scalar expressions specified in input parameters It can be beneficial when you evaluate the maximum value in your dataset for your reporting requirements or data analysis.

Syntax

To use the Power BI Max function, follow the syntax highlighted below:

MAX(<ColumnNameOrScalar1>, <Scalar2>)

It accepts below parameters that include:

Parameter Name	Description
ColumnNameOrScalar1	It is the column name for which the maximum value will be evaluated. You can also specify the Scalar1 value, which may be an expression.
Scalar2	It is the Scalar2 value that can be an expression similar to Scalar1

Note: There are limitations on Power BI max dataset size, which may be up to 1GB for Power BI Pro users. Go through the Microsoft documentation to understand the size limitations.

How to use the Power BI MAX function?

To use the Power BI MAX function, you can follow the below steps:

Step 1: Launch Power BI Desktop on your machine.

Step 2: In the Home tab, click on the Get data option and choose the right data source to import your dataset. Click on the Load button to load it into Power BI.

Step 3: Navigate to the Modeling tab and click on the New measure option.

Step 4: On the formula bar prompt, specify the DAX expression using the MAX function to create a measure. 

Use the syntax for the MAX function to specify the column name for which you want to evaluate the highest value.

Highest Profit = MAX(financials[Profit])

Click on the Commit icon to save the changes. It will create a new measure in your dataset.

Step 5: Navigate to the Visualizations pane and choose a visual icon. Map the newly created measure to the visual in your reporting requirements.

Note: You can review the Power BI MAXX vs. MAX as they serve two different purposes. MAXX function is highly effective when you intend to iterate through each record and find out the maximum value using an expression.

In the next section, we will demonstrate how you can use the Power BI MAX function with the help of a few examples.

Examples

In this section, we will go through different scenarios demonstrating the use of the MAX function in Power BI.

Example #1 – Max value group by

In this example, we will demonstrate the Power BI Max Value Group By to create a measure using the Data Science Job Salaries dataset. The Data Science Job Salaries dataset contains salary information anonymously from professionals all over the world in the AI, ML, and Data Science space to help people make wise choices around AI careers. 

To use the Power BI Max value Group By, follow the instructions below:

Step 1: Open the Power BI Desktop in your system. In the Home tab, click on the Get data option to Import the dataset. Click on the Load button to load it into Power BI.

Step 2: Navigate to the Data pane. Select the table, right-click on it, and choose New measure from the context menu.

Step 3: Write the DAX expression in the formula bar using the MAX function to create a new measure.

Salary Per Job_Title and Experience_Level = CALCULATE(SUM(‘Data Science Job Salaries'[salary_in_usd]), ALLEXCEPT(‘Data Science Job Salaries’,’Data Science Job Salaries'[job_title]))

Once the changes are done, click on the Commit icon to save the changes.

Step 4: Navigate to the Visualizations pane and select the Table visual icon. Drag and drop the newly created measure to this card visual. 

It will create a Table visual in the report canvas in the Power BI.

Similarly, you can also create a Power BI max date by group for your dataset for your reporting requirements. 

Example #2 – Max filter if

Let’s now look at another example of Power BI MAX Filter If using the Sales_Product_Details. The Sales_Product_Details contains the details of sales transactions, diverse product categories, and the geographical spread of fashion trends.

 To use the “Power BI Max filter if,” follow the instructions below:

Step 1: Import the dataset into Power BI using the Get data option available in the Home tab. Click on the Load button to load it into the Power BI data model.

Step 2: Navigate to the Modeling tab. Select the New measure option.

Step 3: Now, write the logic to create the new measure using the MAX function in the formula bar.

–Measure 1

MaxFilterDemo = 

VAR maxdate = CALCULATE(MAX(‘First model'[Date]), ALL(‘First model'[Product_Category]))

RETURN

IF(MIN(‘First model'[Date]) = maxdate, “TRUE”, “FALSE”)

Step 4: Navigate to the Visualizations pane and select the card visual icon. Map the newly created measure to this card visual. 

Once the mapping is complete, you will see the card visuals in the report canvas, as shown below.

Example #3 – Max date

Now let’s see how we can use Max date in Power BI using the Tesla Deaths – Deaths dataset. The Tesla Deaths – Deaths dataset contains an extensive amount of information related to the fatal incidents including the date and location of each crash, model type involved, and if Autopilot was enabled at the time for Tesla vehicle accidents.

Follow the instructions below to create this example:

Step 1: Navigate to Home – Get data in Power BI Desktop. Click on the Load button to load it into the Power BI data model.

Step 2: Navigate to the Modeling tab and choose New measure.

Step 3: In the formula bar, specify the DAX expression using the MAX function to create a new measure in the Power BI Desktop.

In this demo, we have created 3 measures. 

–Measure 1

Latest Casualty = CALCULATE(SUM(‘Tesla Deaths – Deaths'[ Deaths ]), FILTER(‘Tesla Deaths – Deaths’, ‘Tesla Deaths – Deaths'[Date] = MAX(‘Tesla Deaths – Deaths'[Date])))

–Measure 2

Highest Casualty in a day = MAX(‘Tesla Deaths – Deaths'[ Deaths ])

–Measure 3

Date on which highest casualty happened = CALCULATE(VALUES(‘Tesla Deaths – Deaths'[Date]), FILTER(‘Tesla Deaths – Deaths’, ‘Tesla Deaths – Deaths'[ Deaths ] = MAX(‘Tesla Deaths – Deaths'[ Deaths ])))

Click on the Commit icon to save the changes for your measures. 

Step 4: To create a visual using the newly created measures, navigate to the Visualizations pane and select the card visual icon. Drag and drop the newly created measure to this card visual.

Once the mapping is completed, you will see the data visualization in the report canvas, as provided below.

Example #4 – Max month

Let’s see another example for a demo of Power BI Max month using the Waves Measuring Buoys Data dataset. It contains the measured and derived wave data that are collected by oceanographic wave measuring buoys. It has been anchored at Mooloolaba over 30 months.

Follow the instructions below to use the Power BI Max month.

Step 1: Import the Waves Measuring Buoys Data into Power BI Desktop using the Get data feature.

Step 2: Navigate to the Data pane. Select the table, right-click on it, and choose New measure from the context menu.

Step 3: In the formula bar, specify the DAX expression using the MAX function. 

–Measure 1

Month of highest peak energy wave period = CALCULATE(VALUES(‘Coastal Data System – Waves (Mooloolaba)'[Date/Time].[Month]), FILTER(‘Coastal Data System – Waves (Mooloolaba)’, ‘Coastal Data System – Waves (Mooloolaba)'[Tp] = MAX(‘Coastal Data System – Waves (Mooloolaba)'[Tp])))

It will display the month in which the highest peak energy wave occurred.

Step 4: To create a visual using the newly created measures, navigate to the Visualizations pane and select the card visual icon. Drag and drop the newly created measure to this card visual.

Once the mapping is completed, you will see the data visualization in the report canvas as provided below

The card visual will look like the one provided below in the report canvas.

Example #5 – Max of two columns

In this example, we will demonstrate the Power BI distinct sum and Power BI distinct multiple-column filters using the Tech Layoffs 2020-2024 dataset. The Tech Layoffs 2020-2024 dataset contains the worldwide layoffs in tech companies across different industries along with company details, number of layoffs, coordinates, and total money raised in $ values, etc.

In this example, we will focus on a demo of displaying the Max of two columns in Power BI using the Marks in Subjects dataset. The Marks in Subjects dataset contains the marks across the different subjects for two students.

To demo the Power BI Max of two columns, follow the instructions below:

Step 1: Use the Get data feature in Power BI to import the data into the Power BI data model.

Step 2: Navigate to the Modeling tab. Choose the New measure option.

Step 3: In the formula bar prompt, specify the DAX expression using the MAX. 

In this demo, we have created 2 measures as shown below.

–Measure 1

Highest Marks obtained in Physics = MAX(CALCULATE(SUM(‘Marks in Subjects'[Student A]),’Marks in Subjects'[Subjects] = “Physics”),

CALCULATE(SUM(‘Marks in Subjects'[Student B]),’Marks in Subjects'[Subjects] = “Physics”)

)

–Measure 2

Highest Total Marks Obtained = MAX(SUM(‘Marks in Subjects'[Student A]), SUM(‘Marks in Subjects'[Student B]))

Step 4: To create a visual using the newly created measures, navigate to the Visualizations pane and select the card visual icon. Drag and drop the newly created measure to this card visual.

Once the mapping is completed, you will see the data visualization in the report canvas as provided below.

You can also use the MAX function to create a Power BI max of a measure as well.

Important Things to Note

• Power BI MAX function evaluates blank values as 0 values for comparing two expressions. If all the values are blank, then it returns blank.
• For any erroneous expressions in the input parameters, Power BI MAX returns an error.
• It doesn’t support any Boolean expression, such as TRUE/FALSE values.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to Power BI MAX. Here we explain how to use Max function in Power BI, with syntax, examples, and points to remember. You may learn more from the following articles –

• Power BI Count
• Data Types in Power BI
• Power BI Concatenate