Forum Discussion

Medmbchr's avatar
Medmbchr
Icon for Helper IV rankHelper IV
3 years ago
Solved

Return column name with max value (for each row)

Hi,

 

I have a set of 4 columns with different values, and I need to put a 5th column in which I get the name of the column with the maximum value of the 4 columns (for each row).

 

The thing is that I need to do it in DAX, not PowerQuery (hence no transpose or as such).

 

Is there any way to do it?

 

Thanks

Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    You could try

    Col with max value =
VAR SummaryTable =
    UNION (
        ROW ( "Column name", "Column1", "Column Value", 'Table'[Column1] ),
        ROW ( "Column name", "Column2", "Column Value", 'Table'[Column2] ),
        ROW ( "Column name", "Column3", "Column Value", 'Table'[Column3] ),
        ROW ( "Column name", "Column4", "Column Value", 'Table'[Column4] )
    )
VAR Result =
    CONCATENATEX ( TOPN ( 1, SummaryTable, [Column Value] ), [Column name], ", " )
RETURN
    Result

    In the event of a tie it will return a comma separated list of the column names with the max value

4 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    You could try

    Col with max value =
VAR SummaryTable =
    UNION (
        ROW ( "Column name", "Column1", "Column Value", 'Table'[Column1] ),
        ROW ( "Column name", "Column2", "Column Value", 'Table'[Column2] ),
        ROW ( "Column name", "Column3", "Column Value", 'Table'[Column3] ),
        ROW ( "Column name", "Column4", "Column Value", 'Table'[Column4] )
    )
VAR Result =
    CONCATENATEX ( TOPN ( 1, SummaryTable, [Column Value] ), [Column name], ", " )
RETURN
    Result

    In the event of a tie it will return a comma separated list of the column names with the max value

    • Medmbchr's avatar
      Medmbchr
      Icon for Helper IV rankHelper IV
      3 years ago

      Brilliant, thanks! In the event of 2 or more max values, I would like that the last column to be taken into consideration, any way to do that?

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        you could add another column to act as the tie breaker

        Col with max value =
VAR SummaryTable =
    UNION (
        ROW (
            "Column name", "Column1",
            "Column Value", 'Table'[Column1],
            "Tie breaker", 1
        ),
        ROW (
            "Column name", "Column2",
            "Column Value", 'Table'[Column2],
            "Tie breaker", 2
        ),
        ROW (
            "Column name", "Column3",
            "Column Value", 'Table'[Column3],
            "Tie breaker", 3
        ),
        ROW (
            "Column name", "Column4",
            "Column Value", 'Table'[Column4],
            "Tie breaker", 4
        )
    )
VAR Result =
    CONCATENATEX (
        TOPN ( 1, SummaryTable, [Column Value], DESC, [Tie breaker], DESC ),
        [Column name],
        ", "
    )
RETURN
    Result
    • RIDWIV's avatar
      RIDWIV
      Icon for Helper I rankHelper I
      3 years ago

      seriously this is great, life saviour 👍