Highest value by column

[Bauhaus-Arti]

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	R2	R3	R4	R5	R6	Highest
94	94	118	109	74	85	R3
108	105	128	83	72	1066	R6

I hope someone can help me with this problem.

Thank you! 🙂

[Anonymous]

@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")

[Bauhaus-Arti]

@Anonymous They dont show up in the query editor because R1 to R6 are calculated columns. Any idea how i can fix this?

[Anonymous · Accepted solution]

@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])))))