Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
skopcak
Helper I
Helper I

DAX Max value of multiple columns for every row

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

2 ACCEPTED SOLUTIONS
MattAllington
Community Champion
Community Champion

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])))



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

View solution in original post

@skopcak

 

Another solution and more easiest is:

 

Go to Edit Query

 

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

 

max.png

 

 




Lima - Peru

View solution in original post

13 REPLIES 13
Alan_Dzhanibeko
New Member

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
New Member

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

pbi max.png

Anonymous
Not applicable

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
Community Champion
Community Champion

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])))



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

@skopcak

 

Another solution and more easiest is:

 

Go to Edit Query

 

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

 

max.png

 

 




Lima - Peru

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)

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.

 

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
Not applicable

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

Anonymous
Not applicable

thank you , this was the easiest

 

Vvelarde
Community Champion
Community Champion

@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] ) )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

 




Lima - Peru

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

@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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.