Forum Discussion
Solved
Highest value by column
Hi everybody, I'm stuck at the following problem. I have to select the highest score out of six columns and return the column name of the highest score. The table should look like this: R1 R...
Bauhaus-Arti you can try nested if function like below to get maximum value
Maximum = MAX('Table'[R1],MAX('Table'[R2],MAX('Table'[R3],MAX('Table'[R4],MAX('Table'[R5],'Table'[R6])))))
Bauhaus-Arti Please click on edit query
click on add column tab
Select all columns from R1 to R6
Click on statistics dropdown and select maximum (This will give you maximum value from selected columns)
now close and apply
Create a calculated column using switch statement to get the column name
Column =
SWITCH(TRUE()
,'Table'[Maximum]='Table'[R1],"R1"
,'Table'[Maximum]='Table'[R2],"R2"
,'Table'[Maximum]='Table'[R3],"R3"
,'Table'[Maximum]='Table'[R4],"R4"
,'Table'[Maximum]='Table'[R5],"R5"
,'Table'[Maximum]='Table'[R6],"R6"
,"none")
Anonymous They dont show up in the query editor because R1 to R6 are calculated columns. Any idea how i can fix this?
Bauhaus-Arti you can try nested if function like below to get maximum value
Maximum = MAX('Table'[R1],MAX('Table'[R2],MAX('Table'[R3],MAX('Table'[R4],MAX('Table'[R5],'Table'[R6])))))