Forum Discussion

Bauhaus-Arti's avatar
Bauhaus-Arti
Frequent Visitor
6 years ago
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:

R1R2R3R4R5R6Highest
94941181097485R3
10810512883721066R6

 

I hope someone can help me with this problem.

 

Thank you! 🙂

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Bauhaus-Arti
      Frequent Visitor
      6 years ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

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