Calculate MAX from Multiple Columns or Measures

If you have worked with the MAX function in Power BI (dax) you’d see a strange behavior that it doesn’t let you write more than 2 inputs. In this post I’ll share hacks that’ll allow you to calculate the MAX value on multiple columns or measures.

Consider this Simple Data

If I’d like to find the max value from 3 above columns, I can’t use the MAX function. The problem with the MAX function is that it would allow you to write only 2 inputs

Writing MAX on Multiple Columns

The way to get around this problem is to write a measure like this.

Max of more than 2 columns Measure = 
MAXX(
    {
        MAX(Data[Col1]),
        MAX(Data[Col2]),
        MAX(Data[Col3])
    },
    [Value]
)

1. Notice the use of curly brackets. Using those I am forming a 3 row virtual table which will have the max values for each column.
2. The table created will have the default column name as Value. MAXX can now easily find the max value.

This is quite nifty 😎

Writing MAX on Multiple Measures is the same logic as above

MAX of more than 2 Measures = 
MAXX(
    {
        [write any measure 1],
        [write any measure 2],
        [write any measure 3]
    },
    [Value]
)

I actually learnt this trick from a twitter feed from Owen Auger

A few more of these DAX Tricks

1. CALCULATE Function Trick – Calculate Non Blanks Only
2. Calculating Fiscal Week
3. Sort By Column – Examples
4. Calculate SAMEPERIODLASTYEAR for only partial year
5. Why does my RELATED function not work?
6. Why does my IF function not work?
7. Some awesome DAX Keyboard Shortcuts