A Technique for Finding the MAX and MIN across Multiple Columns in Power BI

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.

In this post, I will show you how to find the MAX and MIN value from values across multiple columns. This technique can also be extended to be used in other scenarios.

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 MAX value across multiple columns

Finding the MAX value in a single column is very straight forward. You can e.g. use MAX DAX function over the entire 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. 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 MIN value 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!