DAX Max value of multiple columns

[ericjo]

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

I would appreciate it if you could tell me the Dax function or Power Query that can be expressed as follows.

[ALLUREAN]

Hi, @ericjo 

Try this measure:

Result = 

MAXX(

{

MAX(Table[Column1]),

MAX(Table[Column2]),

MAX(Table[Column3])

},

[Value]

)

You'll need to put all 12 columns and use your table path. Then drop the meaure next to the columns.

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

https://www.linkedin.com/company/77757292/

[ericjo]

Thank you for your answer.
We confirmed that the value was generated using the Dax you sent. Is there a way to add additional columns to the content?

[Anonymous · Accepted solution]

Hi @ericjo ,

I think there should be something wrong in ALLUREAN 's dax code. It will calcualte the max value in each column and then get the max value from twelve columns. It is incorrect. Here I suggest you to try my M code to add Result column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIFYkMoNgBimJgFlG0AlTNSitWJBvMMkFQaYMGGcDo2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Appended Query" = 
    let _Group =  Table.Group(#"Unpivoted Other Columns", {"Index"}, {{"Value", each List.Max([Value]), type number}}) in
    Table.Combine({#"Unpivoted Other Columns", _Group}),
    #"Replaced Value" = Table.ReplaceValue(#"Appended Query",null,"Result",Replacer.ReplaceValue,{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "Value", List.Sum),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

Result is as below.

 

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.