MAX

How to Use the Power BI DAX function MAX

Syntax of the MAX Function

Before we dive into the practical applications of the MAX function, it’s important to first understand its syntax. The MAX function has the following structure:

MAX(❰column❱)

Here, `❰column❱` refers to the column or table that you want to search for the maximum value. For example, if you wanted to find the highest sales figure in a table of sales data, you would use the MAX function as follows:

MAX(Sales[Amount])

Note that the MAX function is not case-sensitive, so you can use uppercase or lowercase letters when referring to columns.

Using MAX with Filters

One of the most powerful features of the MAX function is its ability to work with filters. By adding filters to your MAX function, you can narrow down your search and find the highest value within a specific subset of your data.

For example, let's say you have a table of sales data that includes columns for Date, Salesperson, and Amount. If you wanted to find the highest sales figure for a specific salesperson, you could use the MAX function as follows:

MAX(FILTER(Sales, Sales[Salesperson]=”John Smith”), Sales[Amount])

Here, we've added a filter to the MAX function that only considers rows where the Salesperson column is equal to "John Smith". By doing this, we can find the maximum value for John Smith's sales figures rather than the overall maximum value for the entire dataset.

Using MAX with Other Functions

Another useful feature of the MAX function is its ability to work in conjunction with other DAX functions. For example, you might use the MAX function in combination with the CALCULATE function to find the highest value within a subset of data that meets certain conditions.

Here's an example of how this might work. Let's say you have a table of sales data that includes columns for Date, Salesperson, Amount, and Product. If you wanted to find the highest sales figure for a specific product over a certain time period, you could use the MAX function in combination with the CALCULATE function as follows:

CALCULATE(MAX(Sales[Amount]), FILTER(Sales, Sales[Product]=”Widget” && Sales[Date]❱=”1/1/2021″ && Sales[Date]❰=”3/31/2021″))

In this example, we've used the MAX function to find the highest sales figure for the Widget product, but we've also added filters for the time period (January 1, 2021 through March 31, 2021). By using the CALCULATE function, we can perform this calculation within the context of the specific filters we've applied.

The MAX function is a powerful tool in Power BI that can be used to quickly find the highest value within a column or table. By using filters and other DAX functions, you can narrow down your search and find the maximum value within a specific subset of your data. If you're working with large datasets, the MAX function can be an excellent way to quickly identify key values and trends within your data.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)