Forum Discussion

shahzadmkd's avatar
shahzadmkd
Regular Visitor
8 years ago
Solved

Max Value between multiple column for each row in a Table

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

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      shahzadmkd
      Regular Visitor
      8 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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