Max Value between multiple column for each row in a Table

[shahzadmkd]

Dear All, good afternoon. 

I am trying to creat a calculated colum to get the Max value between 3 columns for each row in a table. In Excel it is very simle by using Max(columnA, ColumnB, ColumnC).

But am not able to do this in DAX. Can any one help me with this. 

Regards

Shahzad

[v-shex-msft]

Hi @shahzadmkd,

You can direct combo use two max function to achieve your requirement.

Max Value = MAX(MAX([Column1],[Column2]),[Column3]) 

Regards,

Xiaoxin Sheng

[shahzadmkd]

Dear Xiaoxin Sheng. 

Thank you for your reply, but if the number of column increase, than it will be difficult to do the nested Max function, for example if more than 50 columns as my data will grow over time and will add many more columns later on.

Do you know how to solve it. 

Regards, have a good day. 

Shahzad

[Vikki]

Hi Shahzad,

The following DAX measure may help:

#MaxOfMultipleColumns =
VAR ValueTable =
UNION(
ROW("Value", Column1(Expression)),
ROW("Value", Column2(Expression)),
ROW("Value", Column3(Expression)),
ROW("Value", Column4(Expression)),
.....

.....
ROW("Value", Column50(Expression))
)
RETURN
MAXX(ValueTable, [Value])

Vikki

[v-shex-msft · Accepted solution]

Hi @shahzadmkd,

You can try to use power query to achieve your requirement.

Prerequisite: table contains index column.

Logic: Remove other columns which not used to compared, convert current row to list and use list.max function to get max value.

Sample:

 Add custom column 'max value'.

 #"Added Custom" = Table.AddColumn(#"Changed Type", "Max Value", each List.Max(Record.ToList(Table.RemoveColumns(#"Changed Type","Index"){[Index]})))

Result:

Full query:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\sample tab.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Index", Int64.Type}, {"C1", Int64.Type}, {"C2", Int64.Type}, {"C3", Int64.Type}, {"C4", Int64.Type}, {"C5", Int64.Type}, {"C6", Int64.Type}, {"C7", Int64.Type}, {"C8", Int64.Type}, {"C9", Int64.Type}, {"C10", Int64.Type}, {"C11", Int64.Type}, {"C12", Int64.Type}, {"C13", Int64.Type}, {"C14", Int64.Type}, {"C15", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Max Value", each List.Max(Record.ToList(Table.RemoveColumns(#"Changed Type","Index"){[Index]})))
in
    #"Added Custom"

Notice:
1. if table contains multiple not needed columns, use {"Column Name1","Column Name2",...} to replace above "Index" part.
2. You only need to modify "Index" part to keep only number columns calculated in this formula.

Regards,

Xiaoxin Sheng

[BenkenH]

Thank you very much - it shows on my end as desired. It however remains red, not green... and I can't seem to filter out a reason within the first 1000 preview records (large dataset)... any ideas what may be causing the red line under the headers for this max column? null values have been filtered out in previous steps of power query.

[Anonymous]

this code seems very expensive to run in Power query, it operates row by row? 

[BenkenH]

I am also thinking with big datasets to perhaps use an alternative solution... perhaps a simpler conditional formula... if column 1 > column 2, write column 1 else column 2 for instance...