DAX Max value of multiple columns for every row

[skopcak]

Hello

I am trying to find the max date per row for a series of columns.

In excel it is as simle as MAX(A1:L1).

Example:

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	(result)
2	5	1	1	0	2	5	8	2	0	1	2	8
0	0	0	2	0	0	0	0	0	1	0	1	2

I need a way to determine the value of 12 columns

Thanks in advance

[MattAllington · Accepted solution]

Edit: I wrote this reply before seeing the previous post.  

You could write a calc column to do this but it will be ugly.  the MAX function only takes 2 arguements, so you would need to do a number of nested MAX statements (about 11 in total).

The key to your question is "in Excel it is simple".  Power BI is not the same as Excel and you need a different approach.  I would do the following.

Unpivot the data like this

Row ID, Column ID, Value

1,1,2

1,2,5

1,3,1

1,4,1

etc

Then you can place the row number in a visual (say a row on a table) and then write a measure like this.

myMax =  maxx(VALUES('table'[Column ID]),CALCULATE(max('table'[Value])))

[Alan_Dzhanibeko]

Hello. I have got same issue.
Me decision was nest:
Measure =

MAXX (

    UNION (

        ADDCOLUMNS ( 'Column', "Date", MAX ( 'Table1'[Date] ) ),

        ADDCOLUMNS (

            'Column',

            "Date", MAX ( 'Table2'[Date] )

        ),

        ADDCOLUMNS (

            'Column',

            Date",

                CALCULATE (

                    MAX ( 'Table3'[Date] )           

                )

        )

    ),

    [Date]

)

So, i made table by union. inside of union i have 3 tables made by addolumns. 
Looks quite complicated may be

[Neri]

It's probably too late for this solution, but for future reference:

[Anonymous]

This should be a simple solution. How this functionality is not available in a tool that is apartenly ahead of all others in terms of BI is beyond me.

[MattAllington · Accepted solution]

Edit: I wrote this reply before seeing the previous post.  

You could write a calc column to do this but it will be ugly.  the MAX function only takes 2 arguements, so you would need to do a number of nested MAX statements (about 11 in total).

The key to your question is "in Excel it is simple".  Power BI is not the same as Excel and you need a different approach.  I would do the following.

Unpivot the data like this

Row ID, Column ID, Value

1,1,2

1,2,5

1,3,1

1,4,1

etc

Then you can place the row number in a visual (say a row on a table) and then write a measure like this.

myMax =  maxx(VALUES('table'[Column ID]),CALCULATE(max('table'[Value])))

[Vvelarde · Accepted solution]

@skopcak

Another solution and more easiest is:

Go to Edit Query

Select your 12 columns and go to add Column -- Statistics--Maximum

[Doug_M]

Was looking for a way to calculate Range when values were sometimes positive and sometimes negative in two seperate columns.  This solution worked perfectly (created new Max column, then new Min column based on above then final step was Max column - Min column for a new "Range" column)

[cazforshort]

Why is it that when I select more than one column the option gets greyed out? Works fine for single columns though. All whole number columns too.

[LDomingues]

If you want to do the same but for dates (for example, the latest/earliest date in 12 columns):

Go to Edit Query

Select your 12 columns and go to add Column -- Date--Latest/Earliest

[Anonymous]

@LDomingues from the query editor,selecting my date columns, going to the "Add Columns" ribbon and then selecting "Latest" worked great! Thank you!

[Anonymous]

thank you , this was the easiest

[Vvelarde]

@skopcak

Try with this calculated column:

Max =
MAX (
    Table1[Column12],
    MAX (
        Table1[Column11],
        MAX (
            Table1[Column10],
            MAX (
                Table1[Column9],
                MAX (
                    Table1[Column8],
                    MAX (
                        Table1[Column7],
                        MAX (
                            Table1[Column6],
                            MAX (
                                Table1[Column5],
                                MAX (
                                    Table1[Column4],
                                    MAX ( Table1[Column1], MAX ( Table1[Column2],Table1[Column3] ) )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

[ilamp]

Simpler and can be achieved in DAX. This is what I did as well. 

[jengwt]

@skopcak You could try Vverlarde's nested MAX method, particularly if you were trying to take the max of multiple columns from across multiple tables. However, I would use SWITCH statements instead of nested MAXs. Nested statements tend to be very inefficient.