Finding the Highest and Lowest Values Across Many Columns in Power BI using DAX

When working with data in Power BI, sometimes you need to find the highest (maximum) and lowest (minimum) values across several columns. This task is straightforward in programs like Excel where you can just use the MAX and MIN functions with multiple arguments like so: MAX(columnA, columnB, columnC). However, in Power BI, using DAX (Data Analysis Expressions) to achieve the same result requires a different approach.

This is a common challenge for many people transitioning from Excel to Power BI for their data analysis needs. In Excel, it’s easy to compare multiple columns and find the highest or lowest values. But in Power BI, you need to use DAX, which has a different set of functions and a different syntax.

In this guide, I’ll walk you through a handy method I discovered to find the maximum and minimum values across multiple columns in Power BI using DAX. This method is not only efficient but also opens the door to other advanced data analysis techniques you might find useful.

While participating in the 25 days of DAX Fridays! – Second edition challenge by Curbal, I was searching for a good way of finding the MAX value from multiple columns in a table.
During my search I found a nice technique which requires very little DAX code but is perhaps not the most intuitive solution.

Example data

I’m using Power BI for this demonstration. The sample data looks like the following.
The table contains a set of random numbers.

Finding the highest value (maximum) across multiple columns

Finding the MAX value in a single column is very straight forward. You can e.g. use MAX DAX function over that single column. However, finding the MAX value over a set of multiple columns is a bit trickier.

The trick I found was to use DAX function MAXX together with a DAX table constructor.
In order to find the highest number from above table, you can write a measure like this:

Max number =
MAXX (
    {
        MAX ( 'Table'[Col1] ),
        MAX ( 'Table'[Col2] ),
        MAX ( 'Table'[Col3] ),
        MAX ( 'Table'[Col4] )
    },
    [Value]
)

The result looks like this

So, how does it work? Let’s break it down.

The MAXX function takes a Table as it’s first parameter, and an expression as it’s second parameter. The MAXX function is an iterator function, meaning that it will go through all rows in the specified table and return the maximum value based on the supplied expression.

In this case, I’m defining a new table using the DAX table constructor using { }. The table consists of 4 rows and one column, where each row represents the maximum value of each column. Evaluating the table constructor in DAX studio returns the following values.

As you can see, the table constructor outputs a single column with a default name of Value.
This column is then used as an expression in the MAXX function, seen in my previous example code. Because MAXX is an iterator function, it will iterate over the 4 lines of the column Value and return the MAX value found.

Finding the lowest value (minimum) across multiple columns

The same way that we can find the MAX value from multiple columns, we can also find the MIN value. In order to find the MIN value we use a the functions MINX and MIN.

The code to find the MIN value over the same dataset looks like this

Min number =
MINX (
    {
        MIN ( 'Table'[Col1] ),
        MIN ( 'Table'[Col2] ),
        MIN ( 'Table'[Col3] ),
        MIN ( 'Table'[Col4] )
    },
    [Value]
)

Other variations

You can use the same technique for other scenarios as well. For example, you can use it to find

• Max/Min of the average column values
• Max/Min sum of columns
• Max/Min of median column values

I’m sure there are other interesting ways to use this technique as well. Do you know any?
Please comment below!

I found this technique originally from https://goodly.co.in/max-multiple-columns-measures-powerbi-dax/

Thank you for reading!