Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Hi @shahzadmkd,
You can direct combo use two max function to achieve your requirement.
Max Value = MAX(MAX([Column1],[Column2]),[Column3])
Regards,
Xiaoxin Sheng
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
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
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
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.
this code seems very expensive to run in Power query, it operates row by row?
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |